Wednesday, December 19, 2007

SQL Nexus Tool - Home

 

What is SQL Nexus?

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

SQL Nexus Tool - Home

PSS SQL Server Engineers : RML Utilities for Microsoft SQL Server Released

 

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

SQL Courses

Large list of database resources from Joe Celko..

SQL Courses

Wednesday, December 12, 2007

Passing the 70-445 BI Exam

Jorg Klein has some great info if you're studying for 70-445, or MS exams in general.

http://sqlblogcasts.com/blogs/jorg/archive/2007/11/15/MCTS-_2D00_-I-passed-the-70_2D00_445-exam_2100_.aspx

And he linked to my article on Sql Server Central.

Thanks Jorg!

SQL Server Engine Tips : SQL Server 2005

 

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:

SQL Server Engine Tips : SQL Server 2005

Rocket Science: OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

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;

SQL Server Engine Tips : OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

MDX Outline Formulas

Essbase to MDX translation.

MDX Outline Formulas

Monday, December 10, 2007

Troubleshooting 64 bit COM+ Apps

One interesting behaviour on 64-bit systems I saw today is how applications are run, and the fact that two command prompts are available (32 & 64 bit).

"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). "

http://searchwincomputing.techtarget.com/tip/0,289483,sid68_gci1218185,00.html

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.

Source: Tips, Tricks, and Advice from the SQL Server Query Optimization Team : Fun for the day - Automated Auto-Indexing!

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:

  1. 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.
  2. 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
  3. 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.
  4. 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.
  5. Do not rely blindly on my performance results. Remember, the one thing that is always true when working with SQL Server is: “it depends”.

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options

Craig Freedman's SQL Server Blog : Scans vs. Seeks

 

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.

Craig Freedman's SQL Server Blog : Scans vs. Seeks