Troubleshooting SQL Server Slowness

The first step in diagnosing SQL Server performance is to determine the physical bottleneck with the most contention. Contention in one of the following areas does not always mean that subsystem is performing poorly. It could just as well be improper utilization due to poor tuning. Nevertheless, identifying the bottleneck is always the first place to start. Luckily, Microsoft does provide some unsupported but very useful tools to help us find this. For these, see the video Using SQLDiag and SQL Nexus. I still recommend reading the article below, as it will help understand the interrelation between subsystems. There are ten main bottlenecks that can cause SQL Server to slow down. They are all interrelated and often fixing one causes another bottleneck to be revealed. CPU Memory Disk Paging Blocking Network IO Process Trimming Old Statistics Fragmentation Recompilation Blocking Blocking is caused by contention of resources. To understand blocking you need to understand locking. Locking occurs in order to ensure users see up-to-date accurate information. If records being updated are also being shown to a user before the update finishes, then inaccurate information is being displayed. The most common reason for blocking is the lack of indexing or queries not written to utilize existing indexes. They mostly occur when update or delete statements are performing a table scan, while an incompatible lock like a select statement tries to read the same records. This script will Show the blocking process This script will show the table involved in the blocking And […] Continue reading ...

Query Which Tables are Partitioned

List out which tables are partitioned, and what partition scheme and partition function they use: [cc lang=”sql”] select as TableName, as PartitionScheme, ps.data_space_id, as PartitionFunction, pf.function_id from sys.tables t join sys.indexes i on t.object_id = i.object_id join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.partition_functions pf on ps.function_id = pf.function_id where i.index_id < 2 [/cc] For a given partition function, list each of the boundary values [cc lang=”sql”] select r.boundary_id, r.value from sys.partition_range_values r join sys.partition_functions pf on r.function_id = pf.function_id where = ‘fnQuarters’ — partition function name order by r.value [/cc]

Turn On Snapshot Isolation to Use Version Store

The following statement turns snapshot isolation on: [cc lang=”sql”] ALTER DATABASE sqlserverplanet SET ALLOW_SNAPSHOT_ISOLATION ON GO ALTER DATABASE sqlserverplanet SET READ_COMMITTED_SNAPSHOT ON [/cc] Once snapshot isolation is on, rows that have been modified will fill the version store. To find the amount of space the version store is using, execute: [cc lang=”sql”] SELECT version_store_in_kb = version_store_reserved_page_count*8192/1024 FROM sys.dm_db_file_space_usage [/cc] Continue reading ...

Find Number of Pages Each Database has in BufferPool

This query shows how many pages each database has in the buffer Pool. This will show you the breakdown of memory allocation for each database. [cc lang=”sql”] SELECT DB_NAME(database_id), COUNT(page_id)as number_pages FROM sys.dm_os_buffer_descriptors WHERE database_id !=32767 GROUP BY database_id ORDER BY database_id [/cc]

Find Procedures using Most Memory DMV

The following query displays the top 10 procedures that use the most memory and disk IO on your system. The query further breaks down the logical vs physical reads. Physical reads are the ones that actually touch the disk, logical reads are the ones that read from memory. The ones that read from disk more often will be the ones that are most likely executed less. Fixing the following queries will most likely alleviate your server’s resources quite a bit. [cc lang=”sql”] SELECT TOP 10 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,ReadsPerExecution = NULLIF(qs.total_physical_reads + qs.total_logical_reads,0) / qs.execution_count ,Executions = qs.execution_count ,CPUTime = qs.total_worker_time ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_physical_reads + qs.total_logical_reads DESC [/cc] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!