April 2014 – Database Consultant

Fixing SQL Server problems

SQL Server is just another Windows application, and like all Applications it ticks mechanically through its work within its environment.

If your data machine has stopped ticking or seems about to, you’re going to have to do something about it. Machines don’t heal. So first take some time to think about what changed just before it started playing up?

Or perhaps you know exactly what the problem is – its just grinding to a crawl under too heavy a load. If so – resist the urge to simply “fix-everything” by throwing every resource available at it and hoping it’ll keep going to the top of the hill. In this case its much wiser to find the squeaky-wheel before doing anything else.

Have you a good backup? take another.
Categorize the issue as either …
1. Something went wrong (undo it) or
2. The database is struggling (tune it).

… and good luck 🙂

How to Start SQL Server Agent via Management Studio

I could not remote onto this customers database-server so looked at my local machines and found the ‘short name’ for the SQLAgent service was ‘SQLAgent$sql2014’.

I recognized the last bit (‘~sql2014’) as my local instance so was able to surmise this code would work for the customer (if there instance was called ‘SomeInstanceName’).

exec xp_cmdshell ‘net start SQLAgent$SomeInstanceName’

** UPDATE **
When a colleague locked up a dev server I was able to free it by restarting the service like this …

xp_cmdshell ‘net start’ — to get list of services

xp_cmdshell ‘net stop “SQL Server Agent (DEV2008r”)”‘ — to stop the agent service in preparation for the next step
xp_cmdshell ‘net stop “SQL Server (DEV2008r2)”‘ — to stop the SQL Server Service NOTE: double-quotes

xp_cmdshell ‘net start “SQL Server (DEV2008r2)”‘
xp_cmdshell ‘net start “SQL Server Agent (DEV2008r2)”‘

Execution Plan – notes

A Clustered Index Scan is similar to a Table Scan. IE: The data is being read row-by-row

A SCAN happens when the optimizer determines that 1) all/most rows need to be returned (so it would be a waste of time reading the index keys), 2) the index is not selective enough, and the optimizer thinks it needs to read all/most of the index. 3) the index stats are found to be out-of-date 4) when the query adds functions to a column’s data, obscuring the columns data from the optimizer.

An (index) SCAN suggests that because a normally sufficient index is NOT sufficient at the moment – more data may be being returned than (normally) needed – suggesting more filtering may be needed – perhaps in the WHERE clause.

A CLUSTERED index seek is beneficial because the data can be read from the index, without having to look at the table.

Kill connections

Here’s a handy piece of code to put before a backup or snapshot-restore, where you need to kill current connections …

--first remove any connections 
USE master 
GO 

DECLARE @kill varchar(8000) = ''; 

SELECT @kill=@kill+'kill '+convert(varchar(5),spid)+';' 
FROM master..sysprocesses 
WHERE dbid=db_id('SomeDatabaseName') 
AND spid>50; 

EXEC (@kill);