Valthor's DB Blog

select * from valthor.druzin

Are your cascading constraints slow ?


If you've been thorough with your database design, you've set up bunch of foreign key constraints on your tables to enforce data integrity, and some of them are even cascading ones. 
You then might find out that the performance up those pescy cascading delete statements are badly affecting the performance of your system. This is because you've probably not defined indexes on those cascading columns (which is something you should probably do).

Then this following script comes in handy, since it actually generates a number of index creation scripts for each of the cascading columns which don't have indexes on them as of now. This is very useful when you're troubleshooting performance in a large system.


SQL Server performance metrics

When troubleshooting performance problems on a SQL server, being able to collect some basic performance metrics easily is key. 

This can actually be achieved using a sql script directly from SSMS, without the need of using perfmon or any third party tool. 

The script below does this for you, collecting data in 10 second internvals, calculating the average value over each interval.  It will give you whole bunch of metrics which can be useful, from Page Life Expectancy, Buffer cache hit ratio, memory grants pending etc etc.


Comma delimeted string as tabular rows


Ok, so you're working with SQL Server and you've got yourself a long string with comma delimeted numbers, and desperately need to join those numbers to your table's columns. You're thinking that you'd wish that you could convert your string into a narrow table with a column and rows containing each comma seperated value.
Who hasn't had that problem, right ?

Well, here's a tiny function that turns excatly that kind of string into a excatly that kind of table ( it also works for simple strings ). It uses SQLServers XML processing to accomplish this, nothing too fancy or complicated but a really nice tool to have in your toolbox.


Have you met Merge ?

I have an ongoing love relationship with the MERGE statement.


In 2003 It was was added to the ANSI-SQL standard, and in SQL serve 2008 this was added to Micrsosfts implementation of SQL, and has been available in Oracle since version 9i.
Still, not a lot of folks use it, mostly because they've never heard of it.
What MERGE does, is to combine conditional DML statements into one statement which uses a one-pass scan on the target table being accessed.