Simons SQL Blog : SQL Server 2005 SP2

A significant part of sql server process memory has been paged out

simonsabin — Tue, 10 Mar 2009 21:52:46 GMT

Do you have the following occuring on your SQL Server system.

If so it is likely you are running SQL Server 2005 or SQL Server 2008 Standard edition on x64 hardware.

It will be causing you serious performance issues when it occurs, as all your database pages will be removed from memory and thus SQL will have to read them again from disk. The MVP community and especially Maciej Pilecki is trying to get Microsoft to do something more proactive about it.

The problem is due to bugs in third party drivers and OS bugs.

According to Microsoft all known bugs have been fixed by the relevant vendors and so you shouldn't be experiencing this issue, if you update your system

If you are still experiencing this, which we believ many people still are, then you must contact customer support (https://support.microsoft.com/oas/default.aspx?iid=246306&iguid=bd81e653-64b3-4bf7-bec9-798309406d4d_2_1&ln=en-gb&prid=9455&gprid=347865) to get the to provide you with details of the updates that you will need to apply to your system. My understanding is that this will not cost you anything, as it is a known bug. (Some are detailed here http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

If after contacting CSS you are unable to get an immediate fix, or they want you to pay please comment and vote on this connect item

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=422322

Or send me email using this form http://sqlblogcasts.com/blogs/simons/contact.aspx

Get the tools the professionals use - ReadTrace is now available for SQL 2005

simonsabin — Tue, 18 Dec 2007 15:31:06 GMT

One gripe about SQL 2005 was that read80trace wasn't available for it.

That has now been rectified with the release of RML Utilities for SQL Server.

This is an application that has a set of utilities for reading, analysing and replaying trace files. This is essential when doing any sort of analysis of a SQL Server system.

I've had a quick look and it looks very sound, I look forward to spending some more time investingating it in depth and see how it compares to some of the other tools out in the market place.

http://blogs.msdn.com/psssql/archive/2007/12/18/rml-utilities-for-microsoft-sql-server-released.aspx

How big is your procedure cache?

simonsabin — Wed, 15 Aug 2007 20:57:53 GMT

There have been continuing reports by most people I speak to about theire procedure cache becoming huge. Especially on 64 bit systems with lots of memory. Memory that you want for your data.

The issue is due to the storinging of compiled adhoc plans. They just aren't getting aged out of the cache.

We are lobbying for there to be a way of limitiing the size of the procedure cache. If I have 8Gb of memory I don't want 2 Gb to be used by the procedure cache. If you agree please vote on it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188

So you don't think it applies to you. I bet you will be suprised, run

select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed

from sys.dm_os_memory_clerks

group by name

order by 2 desc

and see how many MB is used by SQLPlans

Generate the scripts for a database and see what the difference is, I am sure you will be suprised.

You will also find that many of Microsoft tools that don't provide parameterised queries also cause the problem

Vote now your database needs you

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188

SSIS - What is wrong with it

simonsabin — Fri, 27 Jul 2007 22:08:48 GMT

I recently visited a client that was achieving wonders with DTS, I was there to give them pointers on SSIS. I spent a day gogin over SSIS from the control flow to configrations.

A couple of things really got me was that SSIS is a new world that youhave to learn and boy there is a lot to learn, SSIS has many ways of achieving the same thing and many ways of doing it badly.

Whilist SSIS can do a lot, if your familiar with programming why venture away from the activex script.

The interface sucks so why not write it all yourself.

I can't express how I felt after this day, the realisation of how difficult SSIS is was overwhelming. Having used SSIS for 2 years I the number of features that require special attention is huge.

SSIS is great for what it is designed for, but you need to all tricks to really get the power out of it.

Jamie has responsding to similar annoyances on his blog http://blogs.conchango.com/jamiethomson/archive/2007/07/27/SSIS_3A00_-The-backlash-continues.aspx

SSIS : Lookup component doesn't handle data type changes

simonsabin — Tue, 08 May 2007 11:44:42 GMT

I am always trying to optimise the storage I use this often results in changing between tinyint, smallint and int data types as the amount of data I need to capture increases.

Unfortunately the SSIS lookup component doesn't handle such changes very well. Well it doesn't.

If you change the the data types of the column being used in the query of the lookup component it will error saying that data types are incompatible. This is due to inconsistency with the data types of the colum collections used in a component.

I believe the lookup should handle this automatically, if you agree please vote here.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276217

The only work around is to recreate the lookup or to hack changing the SQL a few times to get the lookup to change its internal structures.

Hot fixes are always cumulative

simonsabin — Fri, 30 Mar 2007 10:19:46 GMT

The one thing I learnt when I was at the MVP summit a few weeks ago was that hotfixes are always cumulative, in other words you get all the other hotfixes that have been made prior to your hot fix.

The reason for this is that there is one SQL Server code branch that is used for releasing code. The code changes for the hotfix are applied to the branch and the hotfix package is then built.

You may have noticed that the GDR (General Distribution Release in between a hotfix and an SP) for the Maintenance plan cleanup bug made changes to all sorts of areas including RS, AS not just the tools. This is because the GDR is just a hotfix that is made public.

Tony has blogged about the time it takes to apply the latest SQL Hotfix SQL 2005 SP2 hot fix from windows update takes over 10 minutes to run! This is probably because the cut off for SP2 was quite a while ago and so hotfixes have been piling up so the hot fix actuall contains lots of previous hotfixes.

Be warned. Make sure you have a well structured testing process for applying hotfixes as well as service packs

Free 2GB SQL Server USB Keys

simonsabin — Thu, 29 Mar 2007 14:06:27 GMT

We are holding the next UK SQL User group meeting in Central London on the 26th April from 6pm - 9pm (later if you fancy a drink)

The eveneing will have a SQL event and BI event running side by side with 8 presenters and loads of giveaways including t-shirts, books and USB keys.

If you want to learn more about Performance point or SSIS in the real world register for the BI event

http://sqlserverfaq.com/?eid=95&UK SQL Usergroup meeting - Performance Point, SSIS and more cool BI stuff

Or for sessions on managing change and using SMO from TSQL, Arrays in TSQL and other great SQL topics register for the SQL Event.

http://sqlserverfaq.com/?eid=94&Managing Change, Agile Development and SQL Server Tips and Tricks

We are restricted to only 30 people per event so make sure you register soon.

Update to the taskpad custom report

simonsabin — Wed, 28 Mar 2007 09:21:05 GMT

Thank you to those people that informed me that my taskpad report was incorrectly reporting the backups times against the wrong type of backup.

I have now fixed the report and have also included the backup types that are supported by SQL 2005.

You can downlaod the new version of the taskpad custom report

You will need SQL Server 2005 SP2 to use the custom report.

SQL Server Trace Event Definitions

simonsabin — Fri, 23 Mar 2007 10:18:45 GMT

If you are using Event Notifications or capturing events in Profiler you undoubtedly have noticed that MS have used the same columns (xml elements) for storing different data for each event. For example there is an element called "integer data". You may ask what is stored in here cos it ain't obvious from the name of the element.

Help is at hand but it's not obvious. I spent a while digging through BOL and found the pages you need.

The top level page is called "SQL Server Event Class Reference" and can be found with thi BOL link ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm

This page provides a link to all the pages that detail what all the elements mean for each event class.

In addition you can use the index and go to the pages directly. The name of the page is "<event name> event class" i.e.

"Exception event class" with a BOL link of ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5a45dfa7-51fc-420c-96f6-ce2a1f61b684.htm

Maintenance plan warning for SP2

simonsabin — Thu, 08 Mar 2007 07:54:32 GMT

Due to a regression bug introduced in SP2 some of the maintenance plan tasks that can operate on multiple databases may not perform as expected.

If a maintenance plan component uses TSQL of this nature

USE [DBFred]
GO
DBCC CHECKDB....
GO
USE [DBSimon]
GO
DBCC CHECKDB....

To handle errors in the process the batch is split into multiple commands (split by the GO keyword) so that if one fails the others can still be run.

Because a new the server connection is being made for each statement the context of the connection is always the database specified in the connection string. Rather than the one specified in the previous USE statement.

I have found a partial workaround for the executesql task (the maintenance plan one), which is to open the maintenance plan in BI Studio and change the properties of the connection to not reset the connection.

However this does not work for the Database Integrity component.

I therefore suggest anyone that has upgraded to SP2 and is using the Database Integrity component to change and generate the SQL that would have been used and use either the normal SSIS ExecuteSQL Task or the Maintenance ExecuteSQL Task with the above work around.