Introduction to DMV's in SQL 2005 and 2008 from SQL Saturday!

Download my presentation on Introduction to Dynamic Management Views here

I most recently gave this presentation at SQL Saturday! in Baton Rouge on August 1

Introduction to SSIS presentation from SQL Saturday!

Download my presentation on Introduction to SSIS here

I most recently gave this presentation at the SQL Saturday in Baton Rouge on August 1

Forward Engineering from Visio 2007 without Enterprise Architect edition

To me, one of the most boggling, counter-intuitive and short-sighted decisions was to remove forward-engineering of databases from the base editions Microsoft Visio 2007.

One of the best third party add-ons to Visio therefore, is the Orthogonal Toolbox, a XML-based export utility that, with the right XSLT file, can be used to forward engineer your Visio database diagram.

It isn't perfect, but here's how:

1. Close Visio, download and install the Orthogonal Toolbox: http://www.orthogonalsoftware.com/toolboxaddons.html
2. Download this guy's XSLT file: http://www.dougboude.com/blog/1/2008/11/SQL-Forward-Engineering-with-Visio-2003-Professional.cfm
3. Open your visio diagram, click on the new toolbox button.
4. Select the XSLT file in the first box, select a target in the second.
5. Done. Spits out a nice SQL file.

I had a problem with this tool however - including the attributes (the columns) caused the tool to cause a windows error. I was using Visio 2007 and Vista. Curiously enough, copying my entire diagram (control-A) and pasting it into a new Visio diagram solved that problem.

Also, I learned today that VS 2008 can import a SQL file, like the one generated here, into a database solution. Check constraints and defaults don't get put in, but its a big timesaver for PK's, FK's and tables.

Links:
http://www.orthogonalsoftware.com/toolboxaddons.html (dead)
http://www.dougboude.com/blog/1/2008/11/SQL-Forward-Engineering-with-Visio-2003-Professional.cfm
http://richard.gluga.com/2009/03/no-erd-to-sql-code-generation-in-visio.html (dead)

UPDATE: see comments for more information on generating DDL from a Visio doc

UPDATE: in Vista and Windows 7, try running Visio in XP SP2 compatibility mode.  This cleared up an error in Visio 2007: "Requested Registry Access Is Not Allowed"

UPDATE: If you want the orthogonal software download, try this link here.  Otherwise, try this: http://forwardengineer.codeplex.com/

"Avarice, the spur of industry" - David Hume

How many threads is too much for SQL?

Here's a redacted version of a Q&A I had with one of the devs (who is not a DBA) at my consulting company. I thought it was worth sharing.

I got a question on multithreading in SQL 2005. Do you know much about it? Our client would like to know how many threads can be open before performance starts to suffer.

And my response to my colleague:

That's a really hard question to answer since SQL doesn't operate in application "threads". SQL server, with only one processor (and therefore one Scheduler) can handle many many threads from an application. More processors will enable it to more quickly respond to multiple threads, and SQL 2005 detects processor count automatically and scales. "Open threads" is not really something to measure, as a multithreaded application really doesn't correlate directly to SQL Server. Sure, you can see the threads connections open and close, but to measure performance, the count of threads is really not useful.

If they are worried about CPU capacity, there are a number of perfmon counters and internal dynamic management views (DMV's) to look at to see if CPU is a bottleneck.

In Perfmon, you want to be looking at

\System\Processor Queue Length (shouldn't exceed 2x # of CPU's)
\Processor(_Total)\% Processor Time (self-explanatory, lower number the better)
\Process(sqlservr)\% Processor Time (self-explanatory, shouldn't differ from the above if no other major apps are running on the server)

In DMV's, look for

sys.dm_os_wait_stats (You don't want to see a lot of CPU-related wait types)

Here is a good link from MS: http://technet.microsoft.com/en-us/magazine/2007.10.sqlcpu.aspx

There's obviously much more to look at with SQL server, but not knowing my audience I'm not sure how much detail you want. Sorry for the unclear answer but there really is no hard number of application threads to stay under. It depends a lot upon the other subsystems of the server as well (RAM and physical IO). Sounds like they want to do performance tuning. Sounds like an opportunity.

"You always admire what you really don't understand." -Blaise Pascal

Correct the name of a SQL server in SQL 2005

Renamed a virtual server, but your SQL server instance still reflects the old name?

verify what SQL Server thinks it is by

select @@servername

rename the server with:

sp_dropserver [sqlserveroldname<\instancename>]
go
sp_addserver [sqlservernewname<\instancename>], local

restart the SQL Server Service

verify the new name by select @@servername

Note: this isn't a huge deal in SQL 2005 as it was in previous versions. You'll notice, for example, you can still access your SQL server even if it thinks it is in the wrong name, and the SSMS object explorer looks accurate. But for remote logins and I would assume linked server connections, having those names out of sync could be fun.

MSDN: http://msdn.microsoft.com/en-us/library/ms143799.aspx

EDIT: Note that you cannot rename the instance name of a named instance, only the server name.