Thursday, June 26, 2008

SQL Management Studio Feature Request

Bring back Control-B to move the splitter between the query and results panes! (If you don't know what I mean, or if it's just been a while and you've forgotten, this was a feature in SQL 2000's Query Analyzer tool)

Unfortunately it's not part of Management Studio in SQL 2008 (at least not in RC0) either so I'm not optimistic that this will make its way back into the product. Still, other people seem to feel the same way because there's a feature request for it on Connect.

Do you have a feature requerst for SQL Server? Submit it to Microsoft via Connect. You never know, the SQL team just might agree with you and add it in...

Wednesday, June 25, 2008

When Good Replication Agents Go Bad...

Here's the scenario: You're responsible for replication (you are the DBA after all) and all of your publishers use a dedicated distributor. Now this happens to be in a high traffic datacenter and there are a lot of publications and subscriptions which means there are a lot of replication agents running (think hundreds). One day you're working on spinning up a new server and subscribe it to the handful of publications it needs..and then you start noticing that some distribution agents don't run anymore. No matter what you do - restarting agents, restarting services, or restarting the server - seems to fix the problem. You wonder if your distributor has "hit its limit" and if you need to start talking to your boss about buying new hardware. Meanwhile you're in firefight mode because subscribers aren't getting data, customers are starting to notice that things aren't updating, and you're pulling your hair out wondering how to fix it.

If you've ever been in this scenario, chances are the new distribution agents have caused your distributor to use up its non-interactive desktop heap. Fortunately there's an easy workaround to get things back on track. Microsoft KB article 949296 details the problem and links to KB article 824422 for the workaround. It requires editing the registry so you'll need admin level access to the server. Once in place your distribution agents should all run again and you can tell your boss about how you just prevented the company from having to spend a few thousand dollars on a new distributor.

Friday, June 13, 2008

Notes from SQLSaturday #4 - Orlando "Tweener"

I gave an extended version of my presentation about XML Features in SQL 2005 at SQLSaturday #4 in Orlando on June 7. Aside from a projector SNAFU which caused me to start 10 minutes late my session went well (I think). Attendance was lower than at previous SQLSaturdays, probably due to the longer session format and cost (parking and lunch were not free this time) but I maintain that it's worth going if you learn something you didn't know about - which I did.

Two more SQLSaturdays have been announced: Olympia, WA, on September 1 and Cleveland, OH, on August 9. Community participation is what makes SQLSaturday work so if you're in the area I highly recommend attending...or even submitting a topic to present. I guarantee you'll have a good time meeting other people who are as passionate about what they do as you and learning a few new things about the tools we base our careers on.

Download link for my presentation:
XML Features in SQL2005 - SQLSat

SQL 2008 RC0 Download

RC0 is available for general (non-subscriber) download now:

Monday, June 9, 2008

Use Plan Guides to Circumvent Parameter Sniffing

I ran into a situation today involving parameter sniffing which brought a box to its knees. Until recently I've gotten around parameter sniffing by copying parameter values to local variables but I've since learned about plan guides. Plan guides allow hints to be applied to specific parts of a stored procedure or query (if run using sp_executesql) without having to modify the query itself. Plan guides are available in SQL Server 2005 and 2008.

When you're in firefighting mode a plan guide can be both a safety net and a timesaver because you don't have to risk touching code inside procedures and you can easily disable\drop a plan guide if it doesn't fix the problem. Sometimes you might not have a choice, either, because the query causing the problem might be buried inside of a compiled binary.

As helpful as they are, I look at using plan guides as a stop gap and not a permanent fix. It would be easy enough to apply a plan guide that fixes the problem and move on to the next thing...but in a few months when a developer make a code change or the statistics on a table have changed the plan guide might not make sense anymore and you can find yourself back in firefight mode. My suggestion is to use the plan guide to get around the immediate problem, then implement a long term fix with an alternate option like using WITH RECOMPILE at execution time or put an OPTION (RECOMPILE) on the problematic statement(s).

For more suggestions on how to deal with parameter sniffing see Plamen Ratchev's Weblog:

See BOL topic "Understanding Plan Guides" for specifics about plan guides:

SQL Server 2008 RC0 Released

Hot on the heels of unveling a new product logo for SQL Server 2008 (read about it here), on Friday Microsoft published SQL Server 2008 RC0! So far it's only available as a MSDN\Technet subscriber download. It'll probably make its way onto Connect soon enough. I'm no Microsoft insider, but I'd bet we hear a little something about it during Tuesday morning's keynote at TechEd for IT Professionals in Orlando.

Download links:
SQL Server 2008 RC0 (MSDN subscribers)
SQL Server 2008 RC0 (TechNet Plus subscribers)

Friday, June 6, 2008

Network documentation with SQL 2005 and SYDI

Like most DBAs I wear many different hats at work, one of which is network administrator. Recently I started a project to document our production servers. I know there's some great tools available that do this but they can get pricey and I'm always up for looking into alternative (free) ways to accomplish things. After a bit of searching around I stumbled across SYDI-Server, a free VBScript that uses WMI to gather information about Windows servers with the ability to output results as an XML file. I figured I could leverage SQL 2005's native XML support to work with SYDI's output to produce my documentation.

To hook the two together I set up batch scripts which call SYDI to create one XML file per server, then use osql to call a stored procedure which bulk inserts the file and makes a copy of the XML in a history table.

Here's how to set it up:
  1. Download and extract SYDI-Server on the machine that you'll use to gather your data (In my case I have a dedicated admin server in each of my datacenters to do this)
  2. In the same directory that you extracted SYDI-Server to, create a subdirectory named "output" and share it as "sydi".
  3. Download and extract my scripts in the same directory that you extracted SYDI-Server to. There should be two DOS batch scripts (sydi_all_servers.bat and sydi.bat), a text file (servers.txt), and SQL scripts file (sydi schema.sql). sydi.bat does the work of gathering information for a server and calling the stored procedure to save the results. sydi_all_servers.bat is a wrapper that passes the content from each line in servers.txt to sydi.bat; each call to sydi.bat opens in a new command window (I did it this way so I could gather server information from multiple servers simultaneously).
  4. Install xpFileUtils Lite on your SQL 2005 instance that you want to store all your server documentation on. This is used by the stored procedure which bulk inserts the XML output from SYDI
  5. Create a new database named "SYDI"; apply the script "sydi schema.sql" (extracted in step 3) which will create two tables and the stored procedure which bulk inserts the output from SYDI-Server. (If you prefer to create the tables and procedure in some other DB that's no problem - the batch scripts can handle this)
  6. Modify servers.txt (from step 3) to include the servers you want to document. Each line contains the server name and IP address separated by a tab
Once it's all set up simply execute sydi_all_servers.bat to start documenting your servers. It takes one required parameter - the name of the SQL Server to bulk insert the results into. By default the scripts bulk insert into a database named SYDI. If you created the tables and procedure in some other database (step 5) you can pass its name as the second parameter after the server name.

One note about security...the account used to run the batch scripts will need administrator rights on the servers it documents, write rights to the directory created in step 2, and read rights to the network share (also created in step 2). The scripts make a trusted connection to your SQL Server. In SQL 2005 bulk inserts using trusted connections require some additional steps be done in Active Directory to work properly if your SQL Service is running under a domain account. If you're in this boat then you can work around it in one of two ways: Run your SQL Server as Local System (not a recommended best practice) or use a SQL login. If you prefer to use a SQL login you'll need to change the osql switches in sydi.bat. You'll also need to make sure that the domain account your SQL Server service is using has read rights to the network share created in step 2. I'll cover the bit about bulk inserts using trusted connections in a future post.

Once you've run the batch scripts against all of your servers you can use XML-DML to query the data and build custom reports\documentation. I've included a sample query which shows some high level information about each server. SYDI-Server collects a LOT of information (look at one of the XML output files and you'll see what I mean) so there are lots of possibilities. I could easily see this being exposed to reporting services so there's a friendly way for IT folks to create their own reports. I could also see this being set up as a scheduled task and integrated into an alerting system so that generates notifications when a configuration change is detected. And of course the data is there for ad hoc queries when you need to know something specific about your servers (e.g. performing an audit of who the members of the local administrators group are for all machines, finding machines where security patch X isn't applied, or listing the shares on every machine)

Give it a try and let me know if you use this in any interesting ways.

Download links:
xpFIleUtils Lite
My SYDI scripts