Friday 23 August 2013

1.10 - Performance Of The IN Operator

I recently had to look at the performance of a query that was running with high CPU, and taking over 40+ seconds to run.
The scenario -
A stored proc used a view that joined to a table with a list of numbers from a table. This table was generated from a string that was passed to the stored proc as a string (possibly 10 values) separated by commas. This string was then split into a table via a UDF (table function) and this was then used in the IN operator to filter the number of results (it had to be dynamic). All indexes possible were made available, within the efficient running of the system.

If I changed the IN operator to a != or = this changed the query time to < 200ms from 40+Seconds but this was not fit for purpose as in this instance as the filter had to be dynamic. I then changed the IN statement that was contained in the WHERE clause to be part of a inner join and to join on the predicates that the IN operator was using ....and, wow! the number of logical reads on the table slumped from 4+million to 4 and the time down to 6 secs!! Not too bad as it was an offline, but crucial process.

So the long and the short is to remember; If you can join to it do so, as you may pay the price for utilisation of IN statements.

Friday 16 August 2013

1.09 - API CURSORS and linked servers - Grrrrr!!

Like I always say; Every day is a school day.
A normally well performing system today suddenly started using 99% utilisation constantly. After taking a look I noticed that there were some strange requests on the server which all related to FETCH_APICURSORXXXXXXX. Now I could only see what these were by by querying the sys.dm_exec_cursors dmv. I then looked at profiler and saw the millions of sp_cursorfetch transactions and realised that there was a big issue. This was using a cursor because SQL was using an OLEDB connection on the linked server resulting in millions of rows individually returned  for a sizeable dataset.

After a bit of research OPENQUERY resolves this issue but it doesn't use parameters, so you need to use dynamic sql or call sp_executesql from the target server.

I hope this hopes anyone and reduces the the hours that it took me to sort out.

A good reference : http://www.sql-server-performance.com/2006/api-server-cursors/#comment-3847

Wednesday 7 August 2013

1.08 - Intelllisense Refresh

To force a suggestion = CTRL+J   and to force an intellisense refresh = CTRL+SHIFT+R
- Who knew?

1.07 - SQL SERVER – How to Refresh SSMS Intellisense Cache to Update Schema Changes

Have you ever faced situation where you have just created or modified object but SSMS still shows the error. I quite often face this situation where I come across situation where my SSMS Intellisense Cache is not refreshed or updated. This is indeed very frustrating when you are presenting something on stage as the red underline means an error in graved in many people’s minds and it is hard for them to believe when the code with underline runs successfully.

Here is image of the recent situation. Where I had just dropped index but SSMS Intellisense was still showing that the index exists.
When I face this kind of situation, without wasting time I immediately do following : CTRL + SHIFT + R . This will clear the cache of the Intellisense and it removes the underline from the word. You can also refresh the Intellisense cache by using Edit -> Intellisense -> Refresh Local Cache.

Friday 2 August 2013

1.06 - Old And Forgotten Database Recovery Options

I recently stumbled across some old commands to remove the db status from 'suspect' or 'restoring', although I would always recommend using restore database with recovery initially to remove the recovery issue.
These are :

  1. sp_resetstatus
  2. DBCC DBRECOVER (database) - Removed from SQL 2012
These should not be your first option but when all else fails...