SQL

HA and DR require the same techniques right?

While many of the technologies used for HA and DR are similar (or even the same) HA and DR are two different types of events which should be handled differently. Continued »

Adding column to take taking to long? You’re probably assigning a default.

When you create a column on a database, and you put that column on the right of the table the column shouldn’t be added almost instantly.  However if you are assigning a default value to the column and setting the column to now allow NULL values the operation will take quite a lot longer.  This is because when the operation happens the SQL Server has to write the values to each table.

If you allow the column to have NULL values then the database doesn’t have to write the values to the column so the operation completes very quickly.

Denny

Setting VMware memory settings to improve SQL Server Performance

There is a VMware setting which you can set which should give you a small but noticeable performance improvement in SQL Server performance when that SQL Server is running within a VMware Virtual Machine.

Continued »

VMware and SQL and Lock Pages in Memory

With the recent release of the ability for the Lock Pages in Memory setting to be used on SQL Server 2005 and 2008 Standard Edition I see more and more people shooting them selves in the foot with this setting when running under VMware.  I see this as becoming more of an issue now that this switch is available for Standard edition as I would assume that most virtualized SQL Server installations are done using SQL Server Standard Editions.

Continued »

Back To Basics: Reading an Execution Plan

All to often when helping people look at query performance problems I’ll ask them to look at the execution plan, and see what it says.  Most query performance problems can be resolved simply by looking at the execution plan and seeing where you need to add an index.

All to often I (and others) then get asked, how to I look at the execution plan, and what does it mean?

Continued »

Standalone SQL Agent Update 3/2/2009

I’ve been quiet about the Standalone SQL Agent for a while now.  I haven’t forgotten about the project, in fact I’ve been busily working on it in what little free time I have.  I’ve been working on phase 2 of the project which is adding in the UI to handle job management.  Needless to say, I forgot how much I hate building User Interfaces.  I much prefer building windows services which no one sees.

I’m trying to keep the UI as simple as possible while providing all the functionality that the native job management via SSMS provides; even if not all that functionality is supported by the Standalone SQL Agent at this time.

Even though you can’t download a client installer, or the source (since I haven’t checked and in yet) I figured that I’d post a few screenshots to show where I’m at and get some feedback on it.

The first screenshot here is the job list which comes up after you select the SQL Server instance you want to connect to (yes you can connect to a remote instance so that you can manage SQL Express machines remotely).  It shows the jobs, there current state as queried from the SQL Server, if they are enabled and the Next Run Time.  Currently I’m not getting the Last Run Time back from the SQL Server so that needs to be corrected.

The next screenshot is the job step info page.  This should look very similar to what you see in SQL Server Management Studio or SQL Server Enterprise Manager.

It has the job name, owner, category, and the date information.

The third screenshot (below) are the job steps for the selected job. This screen should also look very similar to what is shown in SQL Server Management Studio and SQL Server Enterprise Manager.

I hope that this looks useful as I’m still plugging away at the UI (I hate making UIs).

I’ve also added in a command line application which will remove the three (so far) stored procedures which are placed within the msdb database.  The service puts these in place when the Agent is started, and I need a way to remove them easily during install, so I put them in a separate executable so that they could be easily removed if needed.

Denny

What should I do when my Windows Admin wants to put an anti-virus on the SQL Servers?

My answer to that one is pretty easy.  Let him / her.  I’m a firm believer that every machine on the network should have anti-virus software installed.  Most anti-virus software is pretty lightweight (especially compared to the amount of hardware that your SQL Server has), and if a virus did get onto the SQL Server the results could be awful.

Continued »

PASS 2008 Wrapup

Well the PASS 2008 summit must be official over as I’m sitting at the airport waiting for my plane.  I was expecting a very long security line here at SeaTac but I was through security within maybe 10 minutes.  While means that the two hours I was planning on standing in line I instead get to sit at Terminal D3 (after just moving from N11 on the far side of the airport) and wait for my flight to Ontario.  I guess the upside is that I got to get some lunch before my flight.

Overall I would have to say that the PASS  2008 summit was a success.  I got some good info about SQL Server, and met a lot of people that I either know from forums or who’s writings I’ve been reading for quite a while now.  Overall the sessions were very informative, and I know that I got something out of just about every session I attended.

Last night a group of MVPs got together for dinner with our MVP lead.  The dinner was a blast with about 30 people attending the dinner.  It was on Friday night so a lot of people had already gone home.  It was great to be able to sit and chat and relax with other people after the rush of PASS being over.

It was great to meet everyone, as well as say high again to people that I met at PASS 2007, SQL 2008 Launch and other conferences.

There were a couple of people that I didn’t have a chance to run across that I wish I did.  Hopefully next year.

See you at PASS 2009 back here in Seattle, Washington.

Denny

VMware Windows XP x64 LSI Driver

So yesterday I was asked to setup a few x64 workstations on our ESX server. The Vista x64 machines detected the LSI controller no problem, but the Windows XP machines couldn’t find the controller, and the drivers that VMware included with ESX (3.0.2) were x86 only drivers.

Continued »

Why is my SELECT COUNT(*) running so slow?

Take a look at the execution plan for your query. You’ll notice that the query is doing an Index Scan (or a table scan), not an Index Seek which is why the SELECT COUNT(*) takes so long. The reason for this is that the COUNT(*) function needs to look at every record in the table.

As a workaround you can use the technique that Microsoft uses to quickly display the number of rights when you right click on the table and select properties.

select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id(’YourTable’)
and spart.index_id < 2

You should find that this returns very quickly no matter how many tables you have.

If you are using SQL 2000 still you can use the sysindexes table to get the number.
select max(ROWS)
from sysindexes
where id = object_id(’YourTable’)

This number may be slightly off depending on how often SQL updates the sysindexes table, but it’s usually corrent (or at least close enough).

Denny

(I was informed that my prior information on this post was not quite accurate, so I have revised the post accordingly.  In a nutshell when doing a SELECT count(*) FROM Table even if the row contains all NULLs the record is still counted.)