Wednesday, December 19, 2007
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.
Wednesday, December 12, 2007
And he linked to my article on Sql Server Central.
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*/
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid), '"')
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid), '"') as full_obj_name
, SUBSTRING(t.text, (t.statement_start_offset/2)+1,
WHEN -1 THEN DATALENGTH(t.text)
END - t.statement_start_offset)/2) + 1) AS statement_text
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;
Monday, December 10, 2007
"Windows gets around these issues by offering two command prompts: one 64-bit and one 32-bit. Environment variables are set according to which command environment is being used.
For example, if you open a command prompt by entering the CMD.EXE command at the Run prompt, Windows will open a 64-bit command prompt. In most cases, the %ProgramFiles% environment variable for the command environment will be set to C:\Program Files. If you run a script, the script can interact with 64-bit applications, but not with 32-bit apps.
On the flip side, if you enter the C:\Windows\SysWOW64\cmd.exe command at the run prompt, you'll be running a 32-bit command prompt. In that case, the %ProgramFiles% environment variable will be set to C:\Program Files (x86). "
The key issue to remember here is that c:\program files should not be used for 32-bit apps or 32-bit COM+ components. Use c:\program files (x86) instead.
Tuesday, December 04, 2007
Tips, Tricks, and Advice from the SQL Server Query Optimization Team : Fun for the day - Automated Auto-Indexing!
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.
Monday, December 03, 2007
SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options
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.