One of the best things about becoming a Microsoft MVP is meeting other MVPs.  I bring this up because last week was the annual MVP Summit in Seattle, WA.  I was really looking forward to meeting Steve Kass.  Steve Kass is one of the smartest SQL Server MVPs I’ve encountered, especially when it comes to SQL questions.  A while back, I noticed that Steve made an interesting recommendation for a hash function that you could use for partitioning that I thought was worth noting.  A hash function would be very useful if you wanted to implement your own variation of a range partition using a hash function rather than the standard sort of range partitioning where colA values of A-H go to partition 1, values of I-P got to partition 2, and so forth.

Steve notes that you could use the following for hashing something small in size:

   CAST(    SUBSTRING(      HASHBYTES(’SHA1′,         CAST(my_col AS NVARCHAR(appropriate_size))),8,1) AS tinyint) 

This is just an off the cuff recommendation from Steve and might need some fine tuning, for example, the CAST might throw off persistence.  However, it’s a good start.

Thanks, Steve, for sharing this and thanks, readers, for sharing any improvements you might develop out in the field.

Technorati Tags: partitioning

If you’ve never read Paul Randal’s blog on the SQLSkills website you should check it out. Paul recently posted a stored procedure that can be used to tell which percentage of a database’s data has changed so you can determine which type of backup to take. The procedure can be scheduled and its output analyzed over time to strengthen your backup and recovery strategy (because we know that you already have one, you just need to improve upon it ).  The cool thing about code like this is that it can be bundled into other maintenance procedures and it can integrate with monitoring and maintenance tools.  For instance, you could run this procedure on a schedule with predefined diff thresholds that would flag the  appropriate type of backup job for execution.  You could then use enterprise monitoring tools like MOM or SCOM to determine the best maintenance windows to guarantee the backups run at the right time, and voila!  You’ve got the beginnings of an automated backup and recovery strategy.

First, the quick answer to your question: use SQL Server Profiler, or the SQL server 2005 database management views (DMVs) for this type of problem. PerfMon can help, but not directly.

As Kevin’s recent posts illustrate, there is certainly no shortage of resources out there that can help you use PerfMon to performance tune your application environment. I’m not going to go into which counters you should use because there’s a limit to what PerfMon can do. Still, it’s a valuable tool, but you should use it more to:

1. Create a performance baseline
2. Determine how your application interacts with the operating system
3. Tune database and instance-level configurations.

In your case, unless the problem query is the only statement running PerfMon won’t [directly] help you here; SQL Server Profiler is better suited to this task, but bear in mind that the performance overhead of tracing statement executions is high (read: be careful when using Profiler to trace SQL executions as it can adversely affect instance performance).

If you’re using SQL Server 2005 you can use the database management views (DMVs) to find more information. Specifically, you can use the sys.dm_exec_query_nnnn views to see cached query execution specifics. I would start with sys.dm_exec_query_plan to see the statement’s plan. Using the XML Showplan information in this view you can determine whether the statement is being recompiled, if it’s performing table scans, and glean lots of other supporting information to see what your next steps should be.

Quest and others have tools that take the guesswork out of this investigation, but I have also posted a number of articles on this site explaining how to leverage these tables to get to the bottom of your bottleneck if you want to roll your own solution. You should also make use of the information in the following publications to ensure you’re aware of what Profiler and the DMVs are telling you:

• SQL Server I/O Basics
• SQL Server I/O Basics Chapter 2
• Troubleshooting Performance Problems in SQL Server 2005
• Statistics Used by the Query Optimizer in Microsoft SQL Server

A while back, Quest Software started a series of webcasts called “The Pain of the Week”.  These webcasts are about a specific area of difficulty for SQL Server DBAs and developers, and how specific Quest tools can help.

In the next webcast, I and my colleague from Quest, Bryan Oliver, will present a methodology and show you how to code faster for faster code using Toad for SQL Server.

Toad for SQL Server is uniquely designed to make work easier and more efficient for anyone coding Transact-SQL scripts and routines. From its top-rated debugger and built-in Intellisense, to its powerful tools for comparing and synchronizing multiple instances of SQL Server, Toad delivers improved performance that will save database developers hundreds of hours per year. 

All of the details for the event, including registration, are here:

     http://info.quest.com/kkblog-pow-codefaster-toadexpert

If you’re interested in coding faster in SQL and Transact-SQL and are open to trying new tools, then please join me for this presentation on April 3rd, Thursday, at 10:00 am central time.

Thanks,

-Kevin

Technorati Tags: TOAD for SQL Server

If you’ve read SQL Server Magazine, then you know that I write a monthly column about free SQL Server tools called Tool Time.  I recently wrote about a set of automation scripts from Microsoft’s internal MIS group that automates all sorts of regular tasks they perform on their SQL Server’s.

Today, I’d like to share some more scripts brought to my attention by Ola Hallengren.  Ola wrote some great scripts for backup, integrity check and index optimization that add to what Microsoft had written. I think that it in some areas has some advantages over Microsoft’s solution.

Ola’s index optimization stored procedure rebuilds indexes online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level and lob existence.  This solution was made about 6 months ago and it has been running in a large production environment since then.  You can see them at:

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html

Ola has put a lot of time in this and would really appreciate your feedback.  You can see more of his work at http://ola.hallengren.com.

I’d love to hear about any other database automation scripts you may have written.  Be sure to let me know so I can share them with the community.

Enjoy!

-Kevin

Technorati Tags: Automation

What you’re asking is partially covered in the Books Online topic entitled Rollbacks and Commits in Stored Procedures and Triggers - the article is worth reading if you haven’t looked at it before. Having said that, I’m not a huge fan of the advice provided because it’s rule-of-thumb-esque; it ignores the fact that in some cases business rules are business rules and “imperfect” code needs to be implemented. Theoretically you shouldn’t ROLLBACK within a trigger. So, just like “If it hurts when you do that, don’t do that” if you don’t want the client to see the transaction count message don’t rollback in the trigger. By the way, whatever you do, DO NOT open a nested transaction within the trigger just to make the error go away, i.e. don’t implement a worse practice to buffer the consequences of a not-the-best practice.

Anyway, so far we’re not having any fun…so let’s leverage a cool feature implemented in SQL Server 2005 to solve your problem. Using TRY…CATCH you can custom-tailor your error message like so:

/* Setup my lame-named table and trigger */
IF EXISTS(SELECT * FROM sysobjects where type='U' and name='MyTable')
DROP TABLE MyTable;
GO
CREATE TABLE MyTable([id] int,[desc] nvarchar(500));
GO
IF EXISTS(SELECT * FROM sysobjects where type=’T’ and name=’MyTableTrigger’)
DROP TRIGGER MyTableTrigger;
GO
CREATE TRIGGER MyTableTrigger ON MyTable AFTER INSERT
AS
BEGIN
IF (SELECT COUNT(*) FROM MyTable)=2 AND @@TRANCOUNT>0
BEGIN
ROLLBACK TRAN;
– RAISERROR(’This is my triggered error message’,16,1);
END
END
GO
/* Open a transaction */
BEGIN TRAN
BEGIN TRY
INSERT INTO MyTable SELECT TOP 2 [id],[name] FROM sysobjects;
END TRY
BEGIN CATCH
IF (SELECT ERROR_NUMBER()) IN(3609,3616)
RAISERROR(’This is my coded error message’,16,1);
ELSE
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF @@TRANCOUNT>0
COMMIT TRAN;

Running that code in SQL Server will return your custom message (remove the comment in the CREATE TRIGGER section to display a different error). So, there are two ways to get what you’re looking for. Oh, and if you’re thinking, “that’s great, but the INSERT is being executed by code” just wrap the INSERT in a stored procedure and have your code call the procedure - you’ll be implementing a certifiable best practice and increasing the likelihood of query plan reuse at the same time.

Hope that helps!

Fragmentation: The scattering of parts of a file throughout a disk, as when the operating system breaks up the file and fits it into the spaces left vacant by previously deleted files.

That’s how the dictionary defines fragmentation, which can be a very real performance problem for certain databases. But when is fragmentation truly a problem, and how can you deal with it? The folks posting on the SQL Server Storage Engine blog have a nice series on fragmentation that can help debunk some myths and show you how to evaluate and fix problems once they arise.

On that note, Kalen Delaney has also posted some good information on why going to lengths to defragment system tables is often effort best expended elsewhere. See her posts on this subject here and here.

Finally, you can read the MSDN article Microsoft SQL Server 2000 Index Defragmentation Best Practices for the official word on the subject.

First of all, welcome to the light, we’ve been waiting for you. Just kidding…(sorta).

The first question you need to answer is what’s been causing you problems? Have you taken a look at Microsoft’s Guide to Migrating from Sybase ASE to SQL Server 2005 whitepaper? The planning phase is critical in ensuring your migration is successful. You should definitely do some due dilligence with regard to equivalent, nonsupported and emulated functions as well as unique datatype conversions, and syntax differences/disagreements to determine what’s going to create problems.

There is certainly no shortage of third-party applications to help you migrate your databases and applications, but this can be the perfect opportunity to gain some deep insight into your environment. Yes, that is a glass-half-full way of looking at it, but it also happens to be true. A third-party application can help, but if something during the migration goes south, you or the vendor’s support are still going to have to investigate your environment to determine what went wrong and why. Wouldn’t you rather know?

Personally, I’m a big fan of testimonials and the type of information you usually find in forums and Google groups. In an article entitled Migrating from Sybase to SQL Server Sayed Geneidy talks about his experience migrating to SQL Server 2000. Look at the sections on data compatibility mode and optimizer hints for some items that frequently cause people headaches during a migration.

Hope that helps!

You’re looking to run a SQL Server profiler trace. Performing a trace is Microsoft’s means of capturing every statement executed against an instance (or specified database(s)), but be aware that this level of information comes with a performance penalty inherent in capturing everything.

Now this is by no means the only way to get what you’re looking for. You could also write a script or a set of scripts leveraging a number of different T-SQL syntaxes/commands/functions to gather this information, although nothing guarantees you’ll see everything like profiler does. Here are three options:

1. For SQL Server 2005 you can use the sys.dm_exec_requests DMV and CROSS APPLY the sys.dm_exec_sql_text function in a loop. The MSDN link for sys.dm_exec_sql_text includes a few sample scripts to illustrate how you can leverage that interaction.
2. For SQL Server 2000 (and 2005 if you’d like) you can use the fn_get_sql function together with the sys.sysprocesses system view to see SQL Sytnaxes and to correlate them with current session information.
3. Using DBCC INPUTBUFFER together with sysprocesses (or sys.dm_exec_requests) in a loop can provide similar information to fn_get_sql.

Note that both fn_get_sql and DBCC INPUTBUFFER are limited by the amount of text that can be returned, so don’t consider these an end-all-be-all solution for SQL monitoring.

If you don’t want to get into writing all the scripts you’ll need, or you don’t have the time and resources to create a process that will maintain the databases, tables, reports, and other related monitoring data there is certainly no shortage of third-party applications vying for your attention.  These products specialize in providing user-friendly, formatted, guided answers and advice to important questions like:

• How long does that statement usually run?
• Who usually runs that statement?
• Has the plan for that statement changed? If so, when and why and who changed it?
• …and the list goes on and on.

Once you start asking those questions, SQLServerPedia can certainly be your resource to get answers, and Quest has an arsenal of tools to help analyze, diagnose and resolve even your most complicated performance or management issues.  Hope to see you back here soon!

This is a really tough problem for people to troubleshoot because there is little useful information available about non-I/O latches.

First things first, what are latches? Latches are lightweight synchronization locks used to coordinate access to data either on disk (PAGEIOLATCH) or in the bufferpool (LATCH, PAGELATCH); the latter is actually a bit of a gray area as data pages in the bufferpool are still tied to pages on disk. So why not call them locks to save confusion? Well, because they’re different. For one, lightweight is usually interpreted as short-lived, however there’s more to it than that. Latches are lightweight because users cannot directly control them (people will argue with this statement, but for the purposes of this short article I’ll leave it at that), information on the latch owner is not fully (read always) available, and latches are granted differently than locks. For a very thorough review of exactly what is unique about latches and how to troubleshoot latching issues, Ken Henderson published the best information I’ve seen to date on waits and blocking issues entitled SQL Server 2005 Waiting and Blocking Issues.

I’ll admit the aforementioned article can be a tough read - especially if you’re looking for summary information, or you don’t have a lot of background in SQL Server. So, in summary:

• There are four main causes of page latch blocking:
  1. IO subsystem performance
  2. Contention on internal allocation system tables
  3. Contention on catalog pages
  4. Contention on data pages
• You will never see a latch wait time greater than 5 minutes (something else that makes latches unique from blocking locks)
• Latches are granted using the First-In-First-Out (FIFO) method
• User table latch contention is the simplest to troubleshoot, and is typically the most common source of confusion with latch contention issues. Frequent modifications to the data in specific tables can create hot spots in a database, or more specifially hot pages. This isn’t a symptom of frequent SELECT operations, but rather INSERTs, UPDATEs, and DELETEs.
• When troubleshooting a latching issue:
  1. Check the latch class description in Books Online, if available.
  2. Examine the latch class for indications of what components or what type of statements may acquire the latch.
  3. Examine the blocked task’s current statement for more hints regarding the latch usage.
  4. Use the suffix of the wait type to determine the mode for the blocked request. This will help identify whether the latch is being acquired for shared access—in which case, it must currently be held for exclusive access. An exclusive request implies that the blocked task’s current session needs to do some update, and this can provide further info regarding resolving the blocking.
  5. Query the following DMVs:
     1. sys.dm_os_wait_stats
     2. sys.dm_os_latch_stats
     3. sys.dm_exec_requests
     4. sys.dm_tran_active_transactions

So, to get to the original question: what are you reading incorrectly? Nothing. In your case you had (screenshots not included since I’m not that well-heeled a blogger yet) two SPIDS, both exhibiting parallel coordination (CXPACKET), shared (PAGELATCH_SH) and update latches (PAGELATCH_UP). Since you were observing queries in a parallel environment and simultaneously saw thread coordination waits, once the threads were complete, the latch was removed and the blocking went away; because of the FIFO behavior of latches, one incompatible latch type will cause the rest to wait. Since SQL Server 2000 SP4, Microsoft’s been identifying latches as the cause of blocking in sysprocesses, however since there is no owner information for certain lock types, the DMVs in SQL Server 2005 will not show you a blocker (for example, in sys.dm_os_waiting_tasks) when the blocking latch type is a SH latch.

For more information on troubleshooting performance problems in SQL Server 2005, read the aptly named Microsoft whitepaper Troubleshooting Performance Problems in SQL Server 2005.