The title of this post is what I can express here for this quick blog post. I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries on SQL Server Box.
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
You can change the ORDER BY clause to order this table with different parameters. I invite my reader to share their scripts.
During several recent training courses, I found it very interesting that Worker Thread is not quite known to everyone despite the fact that it is a very important feature. At some point in the discussion, one of the attendees mentioned that we can double the Worker Thread if we double the CPU (add the same number of CPU that we have on current system). The same discussion has triggered this quick article.
Here is the DMV which can be used to find out Max Worker Count
SELECT max_workers_count FROM sys.dm_os_sys_info
Let us run the above query on my system and find the results.
As my system is 32 bit and I have two CPU, the Max Worker Count is displayed as 512.
To address the previous discussion, adding more CPU does not necessarily double the Worker Count. In fact, the logic behind this simple principle is as follows:
For x86 (32-bit) upto 4 logical processors max worker threads = 256
For x86 (32-bit) more than 4 logical processors max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors max worker threads = 512
For x64 (64-bit) more than 4 logical processors max worker threads = 512+ ((# Procs – 4) * 16)
In addition to this, you can configure the Max Worker Thread by using SSMS.
Go to Server Node >> Right Click and Select Property >> Select Process and modify setting under Worker Threads.
According to Book On Line, the default Worker Thread settings are appropriate for most of the systems.
I just received an email from one of the readers asking for help with error he encountered while attempting to run DMV.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
The above error is usually generated when the user who is trying to run the DMV does not have access to the run the DMV. I suggested him to contact his server admin to grant him VIEW SERVER STATE permissions so that he can run the DMV.
If user does not have VIEW SERVER STATE permissions when he runs any DMV, an error is generated.
When the following script is run by Admin (please note that the user cannot modify its own property in this case), it will give the necessary rights to the user.
GRANT VIEW SERVER STATE TO UserName
After the above script is run on the same DMV, it should not show any error.
This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs that are associated with Transactions.
1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.
2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the current session.
Now, what really interests me is the following observation. These two DMVs , in actual fact, display the distinction between active transactions and current transactions. Current transaction can be active transaction at the time of execution, but not all active transactions are current transactions for current transactions DMV.
Let us take a look at an example where first we will run both these DMVs together in a single transaction and then run them separately. We will notice that in the former case (i.e., when they are run in a single transaction) both of them give same transaction ID in result. While in the latter case (i.e., when they are run separately) they give different transaction IDs in result.
SELECT * FROM sys.dm_tran_active_transactions SELECT * FROM sys.dm_tran_current_transaction /* Begin Transation */ BEGIN TRANSACTION
SELECT * FROM sys.dm_tran_active_transactions SELECT * FROM sys.dm_tran_current_transaction COMMIT /* End Transation */
Let us see the following result image and observe the behaviour we discussed above.
To obtain same transaction ID, both the DMVs should be between BEGIN TRANSACTION and END TRANSACTION. Same transaction ID is preserved between BEGIN and END clause of transaction.
Having said all that, now I am eager to receive answer to this questionfrom my blog readers – In what circumstances do you think this behaviour can be useful?
If possible, please write an article on a real life scenario and send it to me. I will be vary happy to publish it on this blog.
It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.
Before dropping the index it is important to check if index is being used or not. I have wrote quick script which can find out quickly if index is used or not. SQL Server 2005 and later editions have Dynamic Management Views (DMV) which can queried to retrieve necessary information.
We will run SELECT on Employee table of AdventureWorks database and check it uses Indexes or not. All the information about Index usage is stored in DMV – sys.dm_db_index_usage_stats. Let us run following query first and save our results.
GO SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName, sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates FROM sys.dm_db_index_usage_stats sis INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID WHERE sis.Database_ID = DB_ID('AdventureWorks') AND sis.OBJECT_ID = OBJECT_ID('HumanResources.Employee'); GO
Now let us run following two SELECT statement which will utilize Indexes on table Employee.
GO SELECT * FROM HumanResources.Employee WITH (INDEX = 1) WHERE EmployeeID = 1
GO SELECT * FROM HumanResources.Employee WITH (INDEX = 2) WHERE LoginID = 'adventure-works\guy1' GO
Note : WITH (INDEX = Number) is not required but I have used it to make sure that first query uses Index 1 and second query uses Index 2. Both the query will return the same result. Now once again we will run our initial query getting data from sys.dm_db_index_usage_stats and compare our result with initial data.
(Click on image to see larger image)
It is clear from comparing both the result set that when running query on tables it updates sys.dm_db_index_usage_stats and increment column user_seeks.
Above whole process explains that any index usage is stored in the sys.dm_db_index_usage_stats. DMV sys.dm_db_index_usage_stats stores all the usage since SQL Server is restarted. Once SQL Server service is restarted sys.dm_db_index_usage_stats is reset to zero but over the period of the time it updates the values in the columns. If we run our initial query without WHERE condition we can get many rows which contains IndexName and their usage. That will give us idea how many indexes are heavily used. If using WHERE condition we do not find our index in the table it is clear indication that Index is not used much.
If SQL Server services are not restarted in reasonable amount of time and if any index usage is not found, the index should be dropped. Again, make sure you have test your performance after dropping the index. If it gets worst put that index back and continue exercise.
One of my friend involved with large business of medical transcript invited me for SQL Server improvement talk last weekend. I had great time talking with group of DBA and developers. One of the topic which was discussed was how to find out Fragmentation Information for any table in one particular database. For SQL Server 2000 it was easy to find using DBCC SHOWCONTIG command. DBCC SHOWCONTIG has some limitation for SQL Server 2000.
SQL Server 2005 has sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information for the data and indexes of the specified table or view. You can run following T-SQL for any database to know detailed information of the database. SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test_contig'), NULL, NULL , 'DETAILED')
Above query returns lots of information, most of the time we only need to know Tablename, IndexName and Percentage of Fragmentation. Following query returns only three most important details mentioned earlier. I have added an extra condition where results are filtered where average fragmentation is greater than 20%.
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID AND i.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 20
The results will help DBA to make necessary reports.