There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table. [cc lang=”sql”] EXEC sp_updatestats [/cc] The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control: [cc lang=”sql”] — Update all statistics on a table UPDATE STATISTICS Sales.SalesOrderDetail — Update a specific index on a table UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail — Update one column on a table specifying sample size UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT [/cc] Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system. The following script updates all out of date statistics. Set the @MaxDaysOld variable to the number of days you will allow the statistics to be out of date by. Setting the @SamplePercent variable to null will use the SQL Server default value of 20,000 rows. You can also change the sample type to specify rows or percent. [cc lang=”sql”] DECLARE @MaxDaysOld int DECLARE @SamplePercent int DECLARE @SampleType nvarchar(50) SET @MaxDaysOld = 0 SET @SamplePercent = NULL –25 SET @SampleType = ‘PERCENT’ –‘ROWS’ BEGIN TRY DROP TABLE #OldStats END TRY BEGIN CATCH SELECT 1 END CATCH SELECT RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1)) ,TableName = OBJECT_SCHEMA_NAME(st.object_id) + ‘.’ + OBJECT_NAME(st.object_id) ,StatName = st.name ,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1) INTO #OldStats […]
Continue reading ...
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 ...
List out which tables are partitioned, and what partition scheme and partition function they use: [cc lang=”sql”] select t.name as TableName, ps.name as PartitionScheme, ps.data_space_id, pf.name 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 pf.name = ‘fnQuarters’ — partition function name order by r.value [/cc]
Continue reading ...
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 ...
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]
Continue reading ...
