I don't know how big your tables are, but unless you have asynchronous updates of stats turned on then is is possible that you will encounter delays and possibly timeouts...

No need for a cursor:

DECLARE @SQL VARCHAR(MAX)

SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT ''' + name + ''',serverproperty(''servername''),a.*, b.GenTableCode, d.FirstName, d.SurName , e.Firstname as ClientFirstname, e.Surname as ClientSurname,...

I'm in total agreement that in this scenario, and for new functionality in particular, the requirements should first be questioned before spending too much time/money on tuning. Ideally the project...

This makes an interesting read, particularly regarding SQL 2005 SP3's attempts to lower granularity to 1ms, but the article doesn't specifically mention GETDATE():

[url=During startup SQL Server attempts to lower the...

Paul, thanks for following this up - the clarification is greatly appreciated.

Cheers

Chris

Paul White NZ (5/6/2010)

---

Chris Howarth-536003 (5/6/2010)

---

I was surprised to see that this is also the case for a database using the 'Full' recovery model (and where a full backup has...

Bhuvnesh (5/4/2010)

---

Paul White NZ (5/4/2010)[hrHave you considered using a SELECT...INTO statement to create the table at the same time as loading? This can be minimally logged too, and is...

Since you asked (compatibility level 80 or lower):

WITH CTELeft

AS

(SELECT 1 AS ID UNION

SELECT 2 UNION

SELECT 3 UNION

SELECT 4),

CTERight

AS

(SELECT 1...

Paul White NZ (4/7/2010)

---

Chris Howarth-536003 (4/7/2010)

---

If this is the only reason that you are looking to rework the code then I would consider turning off the default trace, scripting it,...

If this is the only reason that you are looking to...

DBCC UPDATEUSAGE - doesn't that only have to be run on SQL 2000 or upon upgrade from 2000 -> 2005?

I thought the issue had been corrected in 2005 onwards?

DBCC UPDATEUSAGE...

I prefer to use system objects that aren't marked for removal from a future release of SQL Server, I prefer not to use undocumented stored procs, and I prefer accuracy...

Great article, this will be very useful.

I spotted a typo on line 65 of your stored proc:

from ::fn_trace_geteventinfo(1) AS X

...should be:

from ::fn_trace_geteventinfo(@traceid) AS X

Cheers

Chris

We keep three days of full backups (one per night) and log backups (taken at 10 min intervals for core databases, 1 hour intervals for others) in a share. We...

If you are a local admin then on a non-clustered instance you can gain System Administrator access to SQL Server by simply adding your Windows account (local or domain) to...