Linchi Shea

Killing a SQL Server thread? Don’t!

Linchi Shea — Thu, 04 Feb 2010 20:09:00 GMT

Sometimes, when you kill a session (i.e. a spid) in a SQL Server instance, the spid just refuses to go away not because it’s doing a rollback. Perhaps, it’s stuck on a certain dependency on something external to SQL Server or it’s just simply stuck for...(read more)

What server is it trying to connect to?

Linchi Shea — Sat, 23 Jan 2010 01:54:00 GMT

It’s common to be called to find out why an app cannot connect to one of your database servers. So you start by checking the server, and it’s working just fine. Then, you check the client machine and its database connectivity to the server, and that is...(read more)

Would you optimize SQL for less performance?

Linchi Shea — Fri, 08 Jan 2010 13:56:00 GMT

What do you mean? Okay, that does sound like an oxymoron, doesn't it? Let's say you are trying to optimize a stored procedure, and your proposed change results in the stored procedure running not faster, but perhaps a bit slower than it currently does....(read more)

Add-Content and Out-File are not for performance

Linchi Shea — Mon, 04 Jan 2010 21:31:00 GMT

When you write Powershell scripts and need to write a text file, you have a number of ways to accomplish that. The often suggested approach is to use cmdlets Add-Content or Out-File. Well, this is not news. But some may not notice that these cmdlets are...(read more)

Bad database practices: allowing apps to connect to the server hostname

Linchi Shea — Mon, 28 Dec 2009 20:32:00 GMT

It’s common to see a client application referencing the hostname of a SQL Server instance in its connection string. For instance, assume that you have a server whose hostname is NYCSQL01, and you install a default SQL Server instance on it. Naturally,...(read more)

Get an overview report on your log shipping setups

Linchi Shea — Tue, 22 Dec 2009 02:45:00 GMT

[Updated Dec 23 to include a T-SQL script at the bottom of the post]. You may have an environment where log shipping is set up on various databases between various servers. Would it be handy to have an overview report highlighting which database is being...(read more)

The Transact-SQL Prime Directive – a bad example

Linchi Shea — Fri, 18 Dec 2009 17:12:00 GMT

A while back, I ranted that the design and implementation of Transact-SQL should be guided by a prime directive that guarantees no interference with the flow of set-based data in Transact-SQL. That was primarily motivated by the fact that no such guarantee...(read more)

Performance impact: Speeding up SMO script generation – the test code

Linchi Shea — Tue, 15 Dec 2009 19:10:00 GMT

The attached is the C# code that I used for generating the test results charted in my previous post . Note that for testing no generated script is actually persisted into a file by this program. The Script() method is applied, but the resulting script...(read more)

Performance impact: Speeding up SMO script generation

Linchi Shea — Sun, 13 Dec 2009 22:18:00 GMT

Generating scripts through SMO can be as simple as walking down the database object tree and applying the Script() method to each scriptable object. Well, that is until you start to try it on a database that has a large number of objects (say a few thousands),...(read more)

Performance impact: UNION ALL views, ANSI_PADDING, and bad query plans

Linchi Shea — Fri, 04 Dec 2009 19:43:00 GMT

Whether or not you specify it explicitly, ANSI_PADDING setting is there when you create a table, and can have an impact on the performance of some queries. If you are not careful, it can even hurt performance big time! Let’s demonstrate that with an extremely...(read more)

Bad database practices: abusing linked servers

Linchi Shea — Sat, 07 Nov 2009 00:56:00 GMT

In SQL Server, it is rather handy to retrieve data from a different SQL Server instance and use the result locally in another SQL statement for further processing. In theory and in the set purists’ fantasy land, it shouldn’t matter where you get your...(read more)

I didn't know you could do this!

Linchi Shea — Fri, 06 Nov 2009 16:50:00 GMT

create proc p_test as -- multiple resultsets select @@servername as ServerName; select 'abc'; select srvname from sysservers; go create table #tmp ( ServerName sysname ) go INSERT #tmp EXECUTE p_test; I did not know that the last statement above (i.e....(read more)

Bad database practices: moving data to procedures vs. moving procedures to data

Linchi Shea — Fri, 30 Oct 2009 04:00:00 GMT

Is it better to move data to procedures or move procedures to data? The answer is, of course, “it depends.” Let’s consider a scenario where you have two SQL Server instances: ServerA and ServerB, and you have a procedure on ServerB (call it procB), but...(read more)

Find the complete call tree for a stored procedure

Linchi Shea — Fri, 23 Oct 2009 03:11:00 GMT

Would it be nice to print out the complete call tree of a stored procedure? By complete call tree , I mean the following: · At the very top level, the call tree should identify all the procedures that are called directly. · For any given level, the next...(read more)

Bad database practices: managing databases without a DBA central inventory

Linchi Shea — Tue, 20 Oct 2009 17:26:00 GMT

Aaron Bertrand has been writing an excellent series about Bad habits to kick , highlighting some of the bad practices, primarily, in the areas of T-SQL coding. I’m going to steal his idea and comment on the bad practices I have seen in managing databases....(read more)