Microsoft SQL Server (and other SQL systems) provide a large amount of data about their servers, users, tables, and stored procedures .
During the corona qurantine , I decided to read some books and share with you what I have learned .
If part of your daily job is to write sql scripts or manage sql server you may want to know more about what’s happening in your envirnment for that , in this blog , I share some of the tips I learned in this book from managing users , memory ,cpu etc…
Link to book : https://www.syncfusion.com/ebooks/sql-server-metadata-succinctly
Memory
SQL Server is designed to manage memory itself, rather than require administrators to allocate the memory. Basically, SQL will greedily take as much memory as it can get but will release memory to the operating system if the OS is needy (low memory situation). You can use the dm_os_sys_memory view to query the amount of memory on the server.
script
SELECT [total_physical_memory_kb] / 1024 as TotalPhysMemoryMB ,
[available_physical_memory_kb]/ 1024 as
AvailMemoryMB ,[total_page_file_kb]/1024 as
PageFileTotalMB ,[available_page_file_kb]/1024 as PageFileAvailMB ,[system_memory_state_desc] FROM [sys].[dm_os_sys_memory]
The system_memory_state_desc fields indicate whether memory is high (SQL can keep using it) or low (SQL needs to release some to operating system). Ideally, there are not a lot of memory-intensive processes running on the SQL server machine.
return hard drive status
You can use the xp_fixedDrives stored procedure or, starting with SQL 2017, the new Dynamic Management view called dm_os_enumerate_fixed_drives. The stored procedure returns two columns: the drive letter and megabytes free. The new view returns the drive path and drive type (usually fixed or network), and the bytes free.
script
if object_id('sys.dm_os_enumerate_fixed_drives')
is not null
SELECT fixed_drive_path,drive_type_desc,
free_space_in_bytes/(1024*1024) as
MB_Free FROM sys.dm_os_enumerate_fixed_drives
else exec xp_fixedDrives
determine where the various databases reside
I wanted to know where generally does the SQL Server Database reside ?
where can i find log and data files ?
for that this script will Show free space and system and database files.
script
IF object_id('sys.dm_os_enumerate_fixed_drives') is not null
IF OBJECT_ID('sys.dm_os_enumerate_fixed_drives') IS NOT NULL
SELECT
mf.type_desc,
mf.name,
mf.physical_name,
fd.drive_type_desc,
fd.free_space_in_bytes / (1024 * 1024) AS MB_Free
FROM sys.master_files mf
JOIN sys.dm_os_enumerate_fixed_drives fd
ON SUBSTRING(mf.physical_name, 1, 3) = fd.fixed_drive_path
WHERE database_id IN (1, 2, DB_ID())
ELSE
BEGIN
CREATE TABLE #tmpDrives (
drive char(1),
free_space_in_bytes bigint
)
INSERT INTO #tmpDrives EXEC xp_fixeddrives
SELECT
mf.type_desc,
mf.name,
mf.physical_name,
'FIXED' AS
drive_type_desc,
ROUND((fd.free_space_in_bytes * 1.0) / 1024, 0) AS MB_Free
FROM sys.master_files mf
JOIN #tmpDrives fd
ON SUBSTRING(mf.physical_name, 1, 1) = fd.drive
WHERE database_id IN (1, 2, DB_ID())
DROP TABLE #tmpDrives
END
You should expect to see that the log and data files are stored on separate drives, and that the tempdb files (could be multiple files) are on their own drive, as well. While this hard drive configuration could vary, those are the generally recommended guidelines for performance purposes.
SQL Server uses the system registry of the server machine to hold several settings, such as the SQL image, startup parameters, or port. You can use the dm_server_registry view to peek at these registry settings.
SELECT * FROM [sys].[dm_server_registry]
Databases on the server
A typical SQL server has multiple databases on it, some required by the server and those for your application data. The sys.databases view provides information about all the databases installed on the server.
script
SELECT
database_id,
[name],
create_date,
CASE compatibility_level
WHEN 80 THEN 'SQL 2005'
WHEN 90 THEN 'SQL 2005'
WHEN 100 THEN 'SQL 2008'
WHEN 110 THEN 'SQL 2012'
WHEN 120 THEN 'SQL 2014'
WHEN 130 THEN 'SQL 2016'
WHEN 140 THEN 'SQL 2017'
WHEN 150 THEN 'SQL 2019'
ELSE 'Unknown version'
END AS SQL_Level
FROM sys.databases
ORDER BY database_id
The SQL tables are stored in a physical disk file (MDF files), and you can determine the files that are holding the current database tables using the sys.database_files view.
SELECT
type_desc,
name,
physical_name
FROM sys.database_files
settings
You can run queries against the sys.configurations view to get the values of the settings. For example, the following SQL query checks whether CLR (common language runtime assemblies) are allowed on this server.
SELECT * FROM sys.configurations WHERE [name]='clr enabled'
EXEC sp_configure 'clr enabled'
EXEC sp_configure 'clr enabled',1
SQL administrators will generally use scripts of sp_configure commands to configure the server.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
SERVERPROPERTY
The SERVERPROPERTY function also provides a good deal of information about the server. It takes a single parameter, the property name, and returns the current property value. For example, the following code snippet shows the edition of SQL Server being run.
select SERVERPROPERTY(‘edition’) as ServerEdition
ServerProperties
SELECT
'SQL Server: ' AS Label,
SERVERPROPERTY('ProductVersion') AS Version,
SERVERPROPERTY('edition') AS ServerEdition,
SERVERPROPERTY('productLevel') AS ServerEdition,
SERVERPROPERTY('MachineName') AS Machine,
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 0 THEN 'SQL and Windows logins'
ELSE 'Windows Authentication'
END AS AuthMode
Sys.databases
The primary view for database information is the sys.databases view. This view holds a row of information for each database in the server.
SELECT * from sys.databases WHERE database_id=db_id()
The function db_id() returns the numeric ID of the current database
The General tab of the Properties page provides some simple status information, such as database size and date of the last backup. This information can be assembled using the sys.databases view and the DATABASEPROPERTYEX() function. the sys.databases view and DATABASEPROPERTYEX() function to duplicate much of the information from the General tab
profile
SELECT
db.name AS databaseName,
DATABASEPROPERTYEX(db.name, 'Status')
AS 'Status',
su.Name AS 'Owner',
db.create_date AS 'Date Created',
CONVERT(varchar, mf.size * 8 / 1024) + ' MB'
AS [Total disk space]
FROM sys.databases db
JOIN (SELECT
database_id,
SUM(size) AS Size
FROM sys.master_files
GROUP BY database_id) mf
ON db.database_id = mf.database_id
LEFT JOIN sys.sql_logins su
ON su.sid = db.owner_sid
WHERE db.database_Id=db_Id()
Backup information
The backup information is retrieved from the backupset table in the MSDB (Microsoft Database) database. We can retrieve the backup information for both the data and the logs
profile
SELECT
CASE type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Logs'
END AS BackupType,
MAX(backup_finish_date) AS LastBackup
FROM msdb.dbo.backupset
WHERE database_name = DB_NAME()
GROUP BY database_name,
type
There is other information available in the backupset table, such as the backup size, whether it is encrypted or not, recovery model, compressed size, etc. Although we are only interested in the backup dates, you might find occasional need to access the other fields.
Size information
The size of any database can be retrieved easily from the sys.master_files view, but the actual use (needed to compute space available) requires a bit more effort. Code Listing 20 provides this information.
SELECT
DB_NAME() AS database_name,
LTRIM(STR((CASE
WHEN sf.dbsize >= pt.reservedpages THEN (CONVERT(decimal(15, 2), sf.dbsize) - CONVERT(decimal(15, 2), pt.reservedpages)) * 8192 / 1048576
ELSE 0
END), 15, 2) + ' MB') AS 'Space Available'
FROM (SELECT
SUM(CONVERT(bigint, CASE
WHEN sf.STATUS & 64 = 0 THEN size
ELSE 0
END)) AS dbSize
FROM dbo.sysfiles sf) AS sf,
(SELECT
reservedpages = SUM(a.total_pages)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id) AS pt
Also , you can check the information about the database files and growth is available from the sysFiles view
SELECT
sf.[name] AS Logical_Name,
CASE
WHEN Status = 2 THEN 'ROWS Data'
WHEN Status = 66 THEN 'LOG'
END AS FileType,
ISNULL(fg.type_desc, 'Not Applicable') AS FileGroup,
size * 8.0 / 1024 AS 'Current Size (MB)',
growth * 8.0 / 1024 AS 'Autogrowth (MB)',
CASE
WHEN maxsize = 0 THEN 'No growth'
WHEN maxsize < 0 THEN 'Unlimited'
WHEN maxsize * 8.0 / 1024 >= CAST(268435456 / 8 AS bigint) THEN '2 TerraBytes'
ELSE CAST(ROUND(maxsize * 8.0 / 1024, 0) AS varchar(20)) + 'MB'
END AS MaxSize,
FileName AS 'Path'
FROM sys.sysfiles sf
LEFT JOIN sys.filegroups fg
ON fg.data_space_id = sf.fileid
section who
Who can edit?
This script give you information list of all users, roles, groups, etc., that can manipulate data with your database
SELECT
pr.name,
pr.type,
p.permission_name
FROM sys.database_permissions p
JOIN sys.database_principals pr
ON pr.principal_id = p.grantee_principal_id
WHERE permission_name IN ('DELETE', 'UPDATE', 'INSERT')
AND state = 'G'
Who can select database objects?
SELECT
pr.name,
pr.type,
p.permission_name,
OBJECT_NAME(major_id) AS AllowedView
FROM sys.database_permissions p
JOIN sys.database_principals pr
ON pr.principal_id = p.grantee_principal_id
WHERE permission_name IN ('SELECT')
AND p.state = 'G'
ORDER BY AllowedView
What can the public role do?
You might also want to see what rights the public role has, since all users inherit these rights
SELECT
p.permission_name,
OBJECT_NAME(major_id)
AS AllowedView
FROM sys.database_permissions p
JOIN sys.database_principals pr
ON pr.principal_id = p.grantee_principal_id
WHERE pr.name = 'Public'
AND state = 'G'
AND class <> 0
ORDER BY AllowedView
List all identity columns
SELECT
OBJECT_SCHEMA_NAME(st.object_id) + '.' + st.name AS [TableName],
ic.name AS KeyName,
t.name AS dataType,
ic.seed_value,
ic.increment_value,
ISNULL(ic.last_value, 0) AS Last_Value
FROM sys.tables st
JOIN sys.identity_columns ic
ON ic.object_id = st.object_id
JOIN sys.types t
ON t.system_type_id = ic.system_type_id
ORDER BY [TableName]
This listing shows tables that have an Identity column, along with the seed value and increment for the column. If the table has rows, the Last_value column will report the last seed number.
default constraints
A database table may have a default value to provide during an INSERT when the corresponding field is NULL. The following script lists all the default constraints and the table and columns they are found in.
SELECT
OBJECT_SCHEMA_NAME(dc.parent_object_id) + '.' + OBJECT_NAME(dc.parent_object_id) AS TableName,
c.name AS ColumnName,
dc.definition
FROM sys.default_constraints AS dc
INNER JOIN sys.columns AS c
ON dc.parent_object_id = c.object_id
AND dc.parent_column_id = c.column_id
ORDER BY tableName, columnName
sys.index
This script will identify all the columns that are used as indexes in the various tables. This can be a handy way to determine whether a new search you want to add is already indexed in the table
SELECT
st.name AS TableName,
i.name AS IndexName,
COL_NAME(ic.object_id, ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.tables st
ON st.object_id = ic.object_id
ORDER BY [TableName], ic.index_column_id, ColumnName
sys.check_constraints
A check constraint is a SQL expression that is applied to a column to validate the type of data allowed in that column. For example, you might use the following LIKE expression to ensure a zip code field only contains five digits. zip LIKE ‘[0-9][0-9][0-9][0-9][0-9]'. List all check constraints :
SELECT
OBJECT_SCHEMA_NAME(dc.parent_object_id) + '.' + OBJECT_NAME(dc.parent_object_id) AS TableName,
c.name AS ColumnName,
dc.definition
FROM sys.check_constraints AS dc
INNER JOIN sys.columns AS c
ON dc.parent_object_id = c.object_id
AND dc.parent_column_id = c.column_id
ORDER BY tableName, columnName
report
to produce a report of tables and columns in your system, along with column information.
SELECT
OBJECT_SCHEMA_NAME(tb.object_id) + '.' + OBJECT_NAME(tb.object_id) AS TableName,
c.name AS ColumnName,
ISNULL(pk.PK, '') AS IsKey,
ISNULL(ic.IdentityColumn, '') AS Identity_Column,
ISNULL(cc.CheckConstraint, '') AS Check_Constraint,
ISNULL(dc.DefaultConstraint, '') AS Default_Constraint
FROM sys.tables AS tb
JOIN sys.columns AS c
ON c.object_id = tb.object_id
LEFT JOIN (SELECT
ic.object_id,
ic.index_column_id,
'PRIMARY' AS PK
FROM sys.tables tb
JOIN sys.key_constraints kc
ON kc.parent_object_id = tb.object_id
JOIN sys.index_columns ic
ON ic.object_id = kc.parent_object_id
AND kc.unique_index_id = ic.index_id
WHERE tb.type = 'U'
AND kc.type = 'PK') pk
ON pk.object_id = tb.object_id
AND pk.index_column_id = c.column_id
LEFT JOIN (SELECT
ic.object_id,
ic.name,
ic.name + ' identity(' +
CAST(ic.seed_value AS varchar(10)) + ',' + CAST(ic.increment_value AS varchar(10)) + ') ' AS IdentityColumn
FROM sys.tables st
JOIN sys.identity_columns ic
ON ic.object_id = st.object_id) ic
ON tb.object_id = ic.object_id
AND c.name = ic.name
LEFT JOIN (SELECT
dc.parent_object_id,
dc.parent_column_id,
dc.definition AS CheckConstraint
FROM sys.check_constraints AS dc
INNER JOIN sys.columns AS c
ON dc.parent_object_id = c.object_id
AND dc.parent_column_id = c.column_id) cc
ON cc.parent_object_id = tb.object_id
AND cc.parent_column_id = c.column_id
LEFT JOIN (SELECT
dc.parent_object_id,
dc.parent_column_id,
dc.definition AS DefaultConstraint
FROM sys.default_constraints AS dc
INNER JOIN sys.columns AS c
ON dc.parent_object_id = c.object_id
AND dc.parent_column_id = c.column_id) dc
ON dc.parent_object_id = tb.object_id
AND dc.parent_column_id = c.column_id
ORDER BY tableName, column_id
Searching for deprecated columns
In early versions of SQL, there were text and image columns called text, nText, and image. These columns (while still supported) were deprecated in SQL Server 2005. The following script allows you to search for deprecated column types and indicates the appropriate replacement column type.
List all columns with deprecated types :
SELECT
t.name,
c.name AS ColName,
'Deprecated: ' + CASE
WHEN tp.name = 'text' THEN 'Replace [text] with varchar(max)'
WHEN tp.name = 'ntext' THEN 'Replace [ntext] with nvarchar(max)'
WHEN tp.name = 'image' THEN 'Replace [image] with varbinary(max)'
ELSE 'Table contains Text,nText, or Image fields'
END AS Msg
FROM sys.columns c
JOIN sys.tables t
ON c.object_id = t.object_id
JOIN sys.types tp
ON tp.user_type_id = c.user_type_id
WHERE t.is_ms_shipped = 0
AND tp.name IN ('text', 'ntext', 'image')
If you are using any of these column data types, you should plan on changing the data type to keep current with SQL Server.
Perfermance
What is happening on the server?
The view sys.sysprocesses provides a list of all connections currently open on the server. the below Table lists some of the columns you can use to query this view.
| Column name | Description |
|---|---|
| spid | SQL Server session ID |
| kpid | Windows thread ID. |
| blocked | spid of session blocking this process |
| waittime | How long process has been waiting (milliseconds) or 0. |
| lastwaittype | String description of last wait encountered. |
| dbid | Database ID (use db_name() to see name) of database. |
| cpu | Cumulative CPU usage time for this process. |
| physical_io | Cumulative disk reads/writes. |
| memusage | Current number of memory pages allocated to process. |
| login_time | When this process was logged in. |
| last_batch | Last time a statement was run by process. |
| open_tran | Current number of open transactions used by this process. |
| open_tran | Current number of open transactions used by this process. |
| status | String description of current status Running Background Runnable Sleeping |
| hostname | Name of the workstation. |
| program_name | Name of the application. |
| cmd | Type of command being executed (SELECT, DELETE, etc.). |
| nt_domain | Windows domain, if using Windows authentication. |
| nt_username | Username, if Windows authentication or trusted connection. |
| loginname | User’s login name. |
| sql_handle | Memory pointer to the currently executing command. |
| stmt_end | Ending offset for current statement. |
| stmt_start | Offset into handle of current statement. |
The information in this table provides the ability to determine what exactly the server is doing, and who is doing it. Some example usages appear in the next few queries. Note that dbid of 1 through 4 are system databases, so activity in those databases is typically done by SQL Services. Database ID number 2 is tempdb, which might be worth checking out if you hit performance issues.
Who is running SQL Management Studio?
SQL Management Studio allows users to run queries, updates, etc., in a database. Typically, developers and database administrators will be using this tool. Any other users might be worth reviewing.
SELECT
loginame,
login_time,
cmd
FROM sys.sysprocesses
WHERE dbid > 4
AND program_name LIKE '%SQL Server Man%'
ORDER BY loginame
Who is blocking others?
You can see who might be blocking other processes
SELECT
'Process ' + STR(sp.spid) + ', user ' + sp.loginame + ' is being blocked by ' + STR(bl.spid) + ' user ' + bl.loginame AS BlockedMsg
FROM sys.sysprocesses sp
JOIN sys.sysprocesses bl
ON sp.blocked = bl.spid
WHERE sp.dbid > 4
AND sp.blocked <> 0
Who has open transactions?
If a user has a transaction open, the tables impacted within that transaction will block other update operations (and possibly select statements, depending on isolation level).
SELECT
'Process ' + LTRIM(STR(sp.spid)) + ', user ' + sp.loginame + ' has ' + STR(sp.open_tran) + ' open transactions'
FROM sys.sysprocesses sp
WHERE sp.dbid > 4
AND sp.open_tran <> 0
What are they doing?
The sql_handle column in the view provides the ability to see what is being done by the session. You can use the sys.dm_exec_sql_text table-valued function to look at the actual work being done.
SELECT
sp.spid,
sp.loginame,
st.text
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) st
WHERE sp.dbid > 4
AND sql_handle <> 0
Worst CPU usage
CPU time is measured by the worker_time value, so we can order by total worker time to identify those plans using a lot of CPU time.
SELECT TOP 5
st.text AS SrcCode,
qp.query_plan,
qs.execution_count,
qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
Worst I/O
The input/output (I/O) totals indicate how often a query needs to read something from the disk. Ideally, in a query, you should read the minimum amount of data needed. When a query uses SELECT * or a lot of table scans, SQL is bringing back more data than is needed. For example, imagine a personnel table that includes a binary image of the person. If your code does a SELECT * from this table, but only displays the name and phone number, you’ve had SQL bring back extra data (the binary image, among other fields), when all it needed was two fields.
SELECT TOP 5
st.text AS SrcCode,
qp.query_plan,
qs.execution_count,
qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
Is another processing blocking your query?
You can investigate the sys.dm_os_waiting_tasks view for the blocked column of your target spid. If some other process is blocking your query, focus on the blocker first. Similarly, if another query has open transactions, that might be the culprit slowing your query down.
SELECT
wait_type,
blocking_session_id,
p.program_name,
p.loginame
FROM sys.dm_os_waiting_tasks wt
JOIN sys.sysprocesses p
ON p.sid = wt.blocking_session_id
WHERE session_id = @@spid
finally the : LIKE clause
The LIKE clause is a powerful SQL feature, allowing a person to find “matches” in a table, rather than exact values. However, it is possible to create a condition where SQL must use a table scan (slower) rather than any indexes to resolve the like expression. This can impact performance when applying the like clause to large tables.
If you were creating a system to allow people to search by last name, you might want to use LIKE as shown in the following.
Beginning with => LIKE ‘Mc%’
Ending with => LIKE ‘%son’
The first LIKE clause will use an index (assuming one exists on the last name column). However, the second clause will require a table scan, which can slow performance by quite a bit. To the user of the system, though, the difference might not be understandable as to why one search is quick and the other quite slow.
Hope you enjoyed learning those tips .