The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators who work with Microsoft SQL Server 2000 and Microsoft SQL Server 2005. The utilities are collectively called the RML Utilities for SQL Server.
With the RML Utilities you can answer questions such as the following:
· Which application, database or login is consuming the most resources, and which queries are responsible for that.
· Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.
· What queries are running slower in today's data as compared to a previous set of data.
PSS SQL Server Engineers : RML Utilities for Microsoft SQL Server Released
Updating a large table is a common scenario. This is often encountered in cases of applications that performs series of tasks in the background or data warehousing loading scenarios. To reduce locking and logging resources, such update statements are broken down into smaller batches or units of work. This has been traditionally done using SET ROWCOUNT setting in SQL Server. Now, SQL Server 2005 provides you with a simpler construct that the optimizer can understand and use efficiently. TOP clause has been enhanced in SQL Server 2005 to support expressions and can be used now in all DML operations. Let's look at a quick example on how to use TOP with UPDATE:
I think sp_msforeachdb might be a solution that's a bit easier to read, but this works too.
Get the top 10 statements that took the most time for each database on a SQL 2005 server.
select coalesce(QUOTENAME(DB_NAME(t.dbid), '"'), '') /* NULL means resource db*/
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid), '"')
+ N'.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid), '"') as full_obj_name
, SUBSTRING(t.text, (t.statement_start_offset/2)+1,
((CASE t.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE t.statement_end_offset
END - t.statement_start_offset)/2) + 1) AS statement_text
, t.*
from (
select *, DENSE_RANK() OVER(PARTITION BY t.dbid ORDER BY qs.total_elapsed_time) as rnk
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as t
where t.objectid is not null
) as t
where t.rnk <= 10
order by t.dbid, t.rnk;
Effectively, this will periodically determine top index candidates for your workload. It currently runs in a recommendation mode, but you can also have it run fully automated if you uncomment a line in the file.
I don't like cursors. Usually there are ways to avoid them, more so in SQL 2005. But if they exist in your environment and are causing performance problems, take a look at this article.
If you have to optimize a cursor for performance, keep the following considerations in mind:
- Always try to replace the cursor by a set-based equivalent first. If you fail to see how, do not hesitate to ask in one of the SQL Server newsgroups.
- If you are really stuck with a cursor, then do NOT rely on the default options. They will result in the slowest of all possible option combinations
- If you think that the FAST_FORWARD option results in the fastest possible performance, think again. I have not found one single test case where it was faster than, or even as fast as, a STATIC cursor.
- Do NOT use the WHERE CURRENT OF syntax of the UPDATE command. Using a regular WHERE clause with the primary key values will speed up your performance by a factor of two to three.
- Do not rely blindly on my performance results. Remember, the one thing that is always true when working with SQL Server is: “it depends”.
Scans vs. Seeks
Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes. These iterators are among the most fundamental ones that we support. They appear in nearly every query plan.
