Great question. Arriving at an accurate answer requires some legwork…

First of all, if you’ve enabled lightweight pooling you might be surprised that this feature might be wreaking havoc on your system; telling SQL Server to run in fiber mode sounds cool, but it’s rarely adviseable and it makes certain applications function erraticaly (SQLXML) or stop working altogether (CLR). Ken Henderson’s the pro in this area, and published a great article on the topic, aptly named The Perils of Fiber Mode.

Now, as for CPU-bound SQL Servers you can determine how your instance is handling requests by looking at DBCC SQLPERF(umsstats) in SQL Server 2000 (again, Ken’s published a highly in-depth article on schedulers called Inside the SQL Server 2000 User Mode Scheduler) or at sys.dm_os_schedulers in SQL Server 2005. For a good blog on how to use sys.dm_os_schedulers, go to Slava Oks’s blog on the topic.

With either version of SQL Server you need to create a baseline, or at least gather information over time to accurately determine whether your system is CPU bound; just determining that there is CPU pressure at a single point in time isn’t a good enough indication to go about upgrading your server. First, you should determine that your application code is tuned and there are no cascading resource bottlenecks freezing up your system. Tools like Quest’s Performance Analysis, together with SQL Tuning can identify inefficient statements over time and allow you to quickly get optimized plan recommendations; you can even link to Benchmark Factory to test the impact of the newly upgraded statements! Once you’ve analyzed and tuned your workload you can use osql, sqlcmd, or even ostress to execute scripts over time and dump their results to a table. Once you have a set of data you should set about determining if, for example in SQL Server 2005, the number of runnable tasks per each scheduler always greater than 1. If you’ve tuned your environment, and the aforementioned statement is still true, you can present a good argument for adding CPU resources to your server.

I’ve recently dealt with a number of cases/queries from DBAs making the same claim (above). What gives? All of these professionals were certain there was an internal issue plaguing their SQL Server when in fact physical file fragmentation was to blame.

Diagnosing and dealing with fragmented files shouldn’t be a last attempt at resovling a performance bottleneck. Rather, file configuration, growth and maintenance procedures should be an integral part of every DBAs planning and maintenance process. Why is fragmentation a problem? Check out this article from sql-server-performance.com to see how fragmentation occurs and how it affects your SQL Server (if you’ve got Quest’s Performance Analysis for SQL Server 6 this information will be presented to you in context-sensitive performance advisory topics). There are a number of tools in the marketplace (like Diskeeper) that can help, but good old-fashioned T-SQL can help overcome the problem just as well.

Quest offers a number of products that can help identify and deal with fragmentation (and a slew of other Disk I/O related slowdowns). For more information as your sales / support engineers about Spotlight, Performance Analysis and Foglight for SQL Server. This triple-threat of database analysis and tuning products interface with a number of Quest tools and dovetail nicely to take the guesswork out of even the toughest performance problems.

I was recently trying to diagnose some problems in one of my SQL Servers when I ran across a problem that I hadn’t encountered before involving ‘nonyielding worker threads’.  SQL Server also produced a dump for me to review.  Unfortunately, the dump didn’t make a lot of sense to me. 

If you encounter one of SQL Server’s mini-dump files and want more information, you’ll be happy to know that there’s a lot of information on-line:

http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx

This URL is also a wealth of diagnostic and internals information that I recommend for any serious SQL Server professional.

Enjoy!

-Kev

P.S. And check out the new CD by Paramore - best alt rock CD of the year IMO.

There is a TON to consider when dealing with dates. Still, things don’t have to be too difficult. Once you know the business your code will serve you have to then decide which date format you should use (look at the CONVERT function in books online and you’ll see that you can choose any format from USA with or without the century (mm-dd-yy | mm-dd-yyyy) to ODBC Canonical (yyyy-mm-dd hh:mi:ss(24h)) with many in between. The Internet is filled with examples of code that will format date values for you (like the FormatDate function in SQL Server Magazine InstantDoc 96608).

There is a great article that deals with all sorts of date conversions, comparisons and calculations on www.databasejournal.com. This should be right up your alley!

Let me start by saying that before you do something like this you should definitely determine why you think it’s a good idea. Foreign keys can slow INSERT operations for example, but the odds are they were created for a reason. Still, if you’ve evaluated all the possibilities and this is something you feel is appropriate, you can use a formatted SELECT statement to generate a script, then execute the script.

Formatted SELECT statements can be a DBAs best friend. Put together with the SQLCMD utility, they can be leveraged to create some very robust maintenance operations. In your case, running a formatted SELECT like the following, output to a file, then using SQLCMD to run the file could make for an automated “disable foreign key checks” routine.


SELECT 'ALTER TABLE ' + QUOTENAME(LTRIM(RTRIM([TABLE_SCHEMA]))) + ‘.’ + QUOTENAME(LTRIM(RTRIM([TABLE_NAME]))) + ‘ NOCHECK CONSTRAINT ALL’ FROM [INFORMATION_SCHEMA].[TABLES] WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(LTRIM(RTRIM([TABLE_SCHEMA]))) + ‘.’ + QUOTENAME(LTRIM(RTRIM([TABLE_NAME])))), ‘IsMSShipped’) = 0 AND [TABLE_TYPE] = ‘BASE TABLE’


Question continued…1) When I issue the create file command, will it be log shipped (I think it will)? 2) Will it fail when the drive specification is not replicated on the secondary servers (I think it will)? 3) What will that impact of that be and how can I work around it? 4) How will log-shipping continue when the secondary datafile is not created (or not created the same) as the primary server? And finally 5) What are your recommendations for doing this with minimal interruption.

The situation you’ve encountered is certainly not rare. At 50,000 feet: yes, you can add a file, yes it can be on different paths on different servers, yes the process will be logged, and yes doing so will throw an error. There’s a fifth yes though, and that’s, “Yes, there’s a simple workaround.”

While the process isn’t as “seamless” as Microsoft says it is at the beginning of the article this Microsoft KB article provides the details you’ll need to add a new database file. To sum up what you’re going to see in the article, you’re going to add the new file, SQL Server’s going to throw an error, then you’re going to manually restore the transaction log backup using WITH MOVE. Once you’ve done that, everything really will be seamless.

This MSDN article has what you’re looking for.

By leveraging OPENROWSET you can take this very simple code template and use it to create what you’re after:


INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\myreportspreadsheet.xls;','SELECT name FROM [Sheet_1$]‘)
SELECT [name] FROM master..sysobjects
INSERT INTO OPENROWSET(’Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=C:\myreportspreadsheet.xls;’,'SELECT name FROM [Sheet_2$]‘)
SELECT [name] FROM AdventureWorks..sysobjects
GO


Before you run the aforementioned queries, you need to first make sure you’ve got an Excel spreadsheet on the C drive called myreportspreadsheet.xls with a column named name on both Sheet1 and Sheet2. If you run this as a scheduled Windows task you can add the file creates to the process, thereby ensuring the files exist before the job runs.

The best way to use rules in SQL Server is not to use them! Microsoft has told us the end is nigh for rules:

CREATE RULE will be removed in a future version of Microsoft SQL Server. Avoid using CREATE RULE in new development work, and plan to modify applications that currently use it. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE.

So, taking their advice, let’s go over how to use CHECK constraints instead. Think of a check constraint as a digital bouncer; just as a bouncer only lets people “on the list” into an exclusive club, a check constraint will only allow data that evaluates to TRUE for its conditions to be input. Now, bouncers aren’t perfect and neither are check constraints. This SQL Server TechCenter page gives some examples of check constraint shortcomings.

Still, let’s say I’ve got a table that holds CUSTOMER information, including Postal Code. You could very easily create a check constraint to verify that the Postal Code is a 5-digit numeric value:

ALTER TABLE Customer ADD CONSTRAINT chkPostalCode CHECK (PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9]‘ );

A while back I used code similar to that listed here by John Sample to implement a check constraint for Order Data by Shipper. Using GeoCode functions to determine the distance between two points, I created a check constraint for a “same day” service based on a preset maximum distance. It’s not rocket science, but in this case we were already using GeoCode functions in other areas, and simple check constraints like these simplified processing and cut down on the amount of code needed to implement business rules.

I’m guessing you’re used to Informix or Firebird SQL. You can’t run that statement in SQL Server because the syntax is not supported as part of SQL Server’s T-SQL language.

Trying to port SQL code between database systems is always challenging. There are ANSI standards in place to safeguard against this, but every subsequent release of the big vendors’ database systems makes it less and less appealing for DBAs and developers to jump through the necessary hoops. Why write my own PIVOT code when I can simply use the SQL Server command?

There are a number of comparison websites on the Internet that help compare basic language elements between database systems. I’ve found that this aptly named website, Comparison of different SQL implementations, helped the developers in my office work through many of their issues. Give it a try!

You can send a multivalue parameter if you use the XML datatype. It would be really cool if you could use Oracle’s trick of sending an array as a parameter, but alas… Still, the XML datatype option is nice and flexible and really quite simple to use. Let’s say you use a very simple example like this one:

/* first I declare my variable and set its value */
DECLARE @Multi_Values xml
SET @Multi_Values ='135‘

/* then I declare my procedure to handle multiple values from one variable */
CREATE PROCEDURE MultivalueXMLParameter(@MultiVals xml)
AS
DECLARE @Multi_Values TABLE (ID int)

INSERT INTO @Multi_Values (ID)
SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @MultiVals.nodes('/MyValues/id') as ParamValues(ID)

(conditional processing goes here)

Now, I could have accomplished the same thing by using a temporary table (gasp), but I think you’ll find that this approach is both less resource-intensive, simpler, and less likely to cause a DBA to have a fit.