I had an interesting debate with a customer during a demo where I said that his assertion, “I don’t need to specify ORDER BY, I have a clustered index on that table” was problematic. You see, defining a clustered index does tell SQL Server to store data in the order stipulated by the clustering key, and many times running SELECTs against that data will return the data ordered as expected. But there are some gotchas you should keep in mind:

• When SQL Server runs a query in parallel, each stream will complete it’s set of work and return its result which could cause your clustered-index-ordered data to appear out of order.
• When another query is already scanning the data when your SELECT statement is run, the data will be read out of the order you’re expecting as your query will piggy-back on the other scan, then come back to the beginning to gather all the data requested.
• If statistics are out-of-date on your clustered index, or SQL Server otherwise chooses to use a non-clustered index to scan the data, the order of the clustered index will not be reflected in the result set.

But don’t take my word for it, check out what Conor Cunningham has to say on the topic, including some sample code to prove the point.

I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

Technorati Tags: sp_configure, SQL Server 2000, SQL Server 2005

The easiest way to do this would be to use the SQLCMD command-line utility to export a query or query file result set, changing the column separator value to a comma.

SQLCMD -S MyInstance -E -d sales -i query_file.sql -o output_file.csv -s ,

Look at the hyperlink listed above for more SQLCMD options that can make automating many of your everyday tasks simple.

You want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint.

From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

From the Oracle side you might want to have a look at the Oracle Migration Workbench.

In this Thursday’s webcast about SQL Server Consolidation, I’ll be covering what Perfmon statistics to gather when planning for consolidation. (I’ve got a blog post describing Performance Monitor in detail if you haven’t used it before.)

Here’s my short list of counters specifically for consolidation projects:

• Physical Disk - Avg. Disk Queue Length - shows how many things are waiting on the I/O subsystem
• Physical Disk - Avg Disk sec/Read - shows how long reads are taking
• Physical Disk - Avg Disk sec/Write - shows how long writes are taking
• Processor - % Processor Time - shows how busy the server’s CPUs are
• System - Processor Queue Length - shows how many things are waiting on CPU power

When I’m gathering these statistics and analyzing the results, I’m most concerned about times when these counters show huge spikes. I’m looking for maxed-out CPUs and very heavy drive access. I don’t care about averages - I want the very worst times of the day.

Before a consolidation project, if a single server has a sudden, massive load on its processors, that load is confined to that single server. One server’s load doesn’t suddenly bring down any other applications.

After consolidation, however, more databases & applications live on the same hardware, and the application that used to live by itself now impacts other applications.

In a perfect world, when I consolidate multiple servers together, their extreme load times never overlap. These would be examples of good consolidation candidates:

• Sales force application with heavy use from 8am-10am (morning reports)
• Reporting application with heavy use from 6pm-9pm (as store closing info comes in)
• Web filtering application with heavy use from noon-5pm (as people get bored and start surfing the web)

All of these applications will have load around the clock, but they don’t have knock-the-box down load at any given time when other apps need power too.

Even if the times don’t overlap, though, we have to watch out for apps that will consume all available power no matter what. An extreme example is a data warehouse that runs nightly loads. During that time window, the server is under very heavy load. There are long time windows where the server’s CPU load may average 100% and its I/O subsystems are responding slower. If we consolidated that server with a few others, the data warehouse load will still use all of the power it can get, and other applications will suffer. Plus, the data warehouse’s backup maintenance window might be during the day - and we can’t slow down our other apps during the day. (Yes, data warehouses are a bad candidate for consolidation, and yes, that’s why I picked them as an example.)

Notice that I didn’t include any memory counters in my Perfmon list. When consolidating multiple SQL Servers together onto the same server with different instances, you can segregate their memory use, so you don’t have to worry about them competing for the same memory. On the other hand, if you’re consolidating into a single instance, you can’t segregate applications into separate memory spaces inside SQL anyway, so there’s no use planning for something you can’t change.

That’s not to say you don’t have to plan for memory during consolidation, but it’s just easier than CPU and storage planning.

If there’s other Performance Monitor counters you like to gather before consolidation, post a comment here. Thanks!

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

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

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.

In principle they are the same and are handled the same by your application. The only difference is that NVARCHAR can handle unicode characters allowing you to use multiple languages in the database. NVARCHAR takes twice as much space to store to allow for the extended character set required by some other languages. So if you are not using other languages then definitley use VARCHAR for character data.