The lost “Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart”

SQLCAT (Sharon Bjeletich) released a MS SQL Server 2005/2008 flowchart to troubleshoot performance issues in 2008.
The document is since long gone and there are lots of forum requests on the web, so I traced this little bugger down.

Altho, SQL Server 2005/2008 is a dead horse (or parrot) by now, but the flowchart could still be of some interest for those who are still forced to work with these by now unsupported versions or simply love flow charts like me.

I still haven’t found one for 2012/2014 sadly.


See Troubleshooting SQL for original PDF.

Troubleshooting SQL



Date issue when storing Perfmon data to SQL Server

It is a rather interesting fact that when you setup Perfmon to log all data to a SQL Server instead of to files, the CounterDateTime column is not automatically of the data type DateTime…its varchar…!

In order to do date operations on the table, here is one solution to use for CounterDateTime:

(CAST(SUBSTRING([CounterDateTime],1,23) AS datetime)

Post-install change of Instance Collation in MS SQL 2008 r2 / 2012

Sometimes people don’t know what they want, until you start bothering them about it.

In this strictly hypothetical case, you relied on your default collation settings picked by the SQL server installer since no other hints had been given by the client.

This choice of collation was of course wrong!

Now you need to change the instance collation, which pretty much wipes all your instance settings and you have to reconfigure much of it (if not all) since it will rebuild your system databases.

You’ll need the installer available. Simply issue the following command:

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD=”r@ndomp4ss0wrd” /SQLSYSADMINACCOUNTS=BUILTIN\ADMINISTRATORS mydomain\myuser /SqlCollation=Latin1_General_CI_AI

This will also reset your DB/LOG locations on SQL Server 2008, didn’t happen for my SQL 2012 instance though.

I encountered the following error message with SQL 2012 when running above command:
“The Windows account “Art\Vandelayblabla” does not exist and cannot be provisioned as a SQL Server system administrator.”

Thats because I had encapsulated the sysadminaccounts with citation marks (“), which apparently sucks. So even if you supply multiple accounts you still can’t use “ for encapsulation, really annoying if you are used to *nix standards/common sense.
For multiple accounts just separate them with whitespace (as in my example).

Reading up