Category: Performance Tuning

SQL Server B-Tree Indexes        Root Level        Intermediate Level        Leaf Level Index Column Selection DMV for analysing Index   sys.dm_db_index_physical_stats(null,null,null,null,null) Clustered Index   Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can…

Read More

Measure IOPs from SQL Server Investigating  performance problems, We may need to look at I/O stats as well. Find how the  Monitoring  I/O on storage system is performing.  Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on…

Read More

Below are the top SQL Server Memory Pressure Counters and their recommended values. Page Life Expectancy – It is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated…

Read More

Free List Stall per Second:  Its a counter available in SQL Buffer Manager. If SQL Server don’t have free pages in buffer cache then a processing request goes in stall and it must wait until pages get free in buffer cache. The recommended value of Free List per Seconds counter is below 2. If there…

Read More

SQL Server Buffer Cache details:-  ———————————————————————————————- select        [DateTimePerformed] = SYSDATETIMEOFFSET() ,      Server_Physical_Mem_MB = os.[Server Physical Mem (MB)] — SQL2012+ only ,      c.[Min_Server_Mem_MB] ,      c.[Max_Server_Mem_MB] ,      p.PLE_s ,   p.Stolen_Mem_MB                                                                                                         ,      ‘Churn (MB/s)’                    =      cast((p.Total_Server_Mem_GB)/1024./NULLIF(p.PLE_s,0) as decimal(19,2)) ,      Server_Available_physical_mem_GB = (SELECT cast(available_physical_memory_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_sys_memory) ,      SQL_Physical_memory_in_use_GB = (SELECT cast(physical_memory_in_use_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_process_memory) ,      p.Total_Server_Mem_GB –May be more or less than memory_in_use because it ,      p.Target_Server_Mem_GB     from ( select        InstanceName = @@SERVERNAME ,      Target_Server_Mem_GB =     max(case counter_name when ‘Target Server Memory (KB)’ then convert(decimal(19,3), cntr_value/1024./1024.) end) ,      Total_Server_Mem_GB  =      max(case counter_name when  ‘Total Server Memory…

Read More

–Below script is for check tempDB usage ——————————————————————————– use tempdb go select * from Sys.dm_db_file_space_usage –Is the version store taking up a lot of space? SELECT version_store_Gb = (SUM(version_store_reserved_page_count)*8/1024./1024.)  , total_tempdb_Gb = sum(total_page_count)*8/1024./1024. , pct_of_tempdb_dedicated_to_version_store = round((sum(version_store_reserved_page_count)*1./sum(total_page_count)*1.)*100.,2) FROM sys.dm_db_file_space_usage; –If version store is very large, need to find out what transactions are holding onto it. –It is not possible to associate versionstore allocation to individual sessions –But these queries…

Read More