SQL Server Area

SQL Traces mixing SQLServer2005 - 2008

2009-08-11T16:58:00.000+02:00

Today was expiriencing some problem opening a Trace File:

Unable to locate trace definition file Microsoft SQL Server TraceDefinition 10.0.0.xml for open trace file... It took me more than 10 min to figure out that the server I was profiling (Using SQLServer 2005 profiler) was a SQLServer 2008. :-p

So you can trace a SQLServer 2008 using a SQLServer 2005 Profiler, but you cannot re-open the same traces using a SQLServer 2005 profiler, you have to use a SQLServer 2008 Profiler instead!

Or if you don't have the 2008 tools installed yet on your locale machine, you can use:

select *
from fn_trace_gettable('\\share\...\TestTrc.trc',default)

It works fine on both SQLServer 2005 and SQLServer2008 SSMS to connect to a SQLServer2005 or SQLServer2008 trace.

Fatal error 8646 occurred when performing an "ALTER PARTITION FUNCTION"

2009-08-07T13:22:00.000+02:00

Today I was just doing some tests to create a partitioned table with a sliding window on an existing table.

So I created my partition function and partition scheme, and did a drop index with move to my partition scheme on the existing table. Everithing (selects, inserts,...) worked well, until I wanted to Alter my Partition function to do an extra RANGE SPLIT. Than I did get a Fatal Error 8646... error message. Fortunately, I remembered some problems with indexes and replication from the past who gave the same "8646" error message. And when looking at the table, I saw there was also an existing non-clustered index. After I dropped the non-clustered index, the problem was solved.

I tried to reproduce the same error with a smaller table, but didn't get any "succes".

SQLServer Parameter sniffing

2009-08-05T14:45:00.000+02:00

We had an issue in two of our stored procedures recently with query execution plan caching. After digging deep in the code base we found what was causing the problem.

One of the great features in SQL Server is its optimizing query processor. There are, however, times when some of the advanced features of the optimizer can cause problems. Sometimes it simply helps too much. One of those is when it automatically uses the parameter values passed into a stored procedure to tailor the execution plan it will construct for a query. This is called "parameter sniffing" and is normally a good thing. It allows the optimizer to take into account the actual values of stored procedure parameters as it builds plans to service queries within the procedure that make use of them. IOW, if you pass a value of "test" for a parameter named @param, then use @param in the WHERE clause of a query within the procedure, the optimizer is smart enough to use "test" when it estimates how many rows will match the WHERE clause as it compiles a plan for the query.
While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is suboptimal (or even downright slow) when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance.
SQL Server 2005 has some features to help combat this, but there are no built-in features for dealing with it in prior releases. There are three simple tricks that you can use to solve this problem:

- the use of "decoy variables”. This means that we filter the query on a local variable value; witch replaces the parameter that we actually pass into the procedure.

CREATE PROCEDURE usp_avoidSniff1(@param int)
AS

BEGIN
Decalre @lclVarParam int

SET @lclVarParam = @param

SELECT x, y
FROM MyTable
WHERE z = @lclVarParam
END

GO

- Filter the query on a dummy parameter, i.e. @template_param, that we never actually pass into the procedure. We always pass in the filter value we want to use via @param and let @template_param retain its default. The optimizer notices that we’re filtering the query against the sales table using @template_param and sniffs its value (which will always be the default value at compilation time) when creating the query plan for it.

CREATE PROCEDURE usp_avoidSniff2(@param int, @param_def int= ‘My optim val’)
AS

BEGIN

SET @param_def = @param

SELECT x, y
FROM MyTable
WHERE z = @param_def
END

GO

- Use the OPTIMIZE FOR statement (SQLServer 2005). Adding this to a query makes sure the plan is optimized for the value you specify. And when all else fails you can even force a particular query plan with the new USE PLAN statement

CREATE PROCEDURE usp_avoidSniff1(@param int)
AS

BEGIN

SELECT x, y
FROM MyTable
WHERE z = @param OPTION(OPTIMIZE FOR (@IDate = Null))

END

GO

SQLServer Locking and deadlocks

2009-07-28T10:44:00.000+02:00

In SQLServer environments with a lot of concurrency, locks, blocks and deadlocks are a very common issue.

To avoid negative performance impact, here are some best practices and monitoring tools to use:

1) Use of WITH (NOLOCK)

Where applicable, use WITH (NOLOCK) in the FROM-clause and in JOIN. It avoids locks on the database while performing read-operations.
Even when retrieving a value for values of local parameters inside a stored procedure, use the WITH (NOLOCK) statement.

Example:

SELECT x.a
, y.b
, …
FROM dbo.MyTableX AS x WITH (NOLOCK)
INNER JOIN dbo.MyTableY As y WITH (NOLOCK)
ON …..

The NOLOCK keyword instructs SQL Server not to wait until any exclusive locks are gone. This improves concurrency on the database but, a resultset might contain data that have not been committed.

In rare cases we can encounter this scenario:
Query session A starts to change data for a in table X. Milliseconds later session B starts to read these data with the WITH (NOLOCK) option. Data, as changed by session A are shown to user B. But then user A cancels his action and the transaction is rolled back. At this point user B can see data on his screen that have not been committed.
To avoid problems with uncommitted data (dirty reads) we should not use the WITH (NOLOCK) option when retrieving data that will be used in calculations and batches or on reports.

2) Always check that the columns in your WHERE-clause are covered by an index.

If your statement takes more than 1 second, use the sp_lock command in SQL Server to check on exclusive locks (X)

3) Use of Cursors

Do not use cursors in Stored Procedures. They are too expensive for the DB performance. But sometimes are cursors required.

4) Keep transactions short

Put only the strictly necessary commands in the same transaction, and split up all commands in as much transactions as possible!

5) During transactions, don't allow any user input. Collect it before the transaction begins.

6) Execute inserts/ updates on the same tables in the same order to avoid deadlocks.

7) Choose your clustered index wisely:

a. Choose a very narrow index which is very unique

b. If the clustered index contains more then 1 column, place them in the right “fine graining” order.

c. Avoid lock escalations during the update

8) Avoid page splits:

a. Use a correct fillfactor
b. Again choose your clustered index wisely!!!

9) Use the right monitoring tools when necessary:

a. Profiler traces (DeadLocks + DeadLock chains + Deadlock Graph, lock timeouts).

b. Trace flags: To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).

DBCC TRACEON (3605,1204,-1)

Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server's resources unnecessarily, hurting performance.

10) Change the default deadlock behavior when appropriate

You can this by using the following command:

SET DEADLOCK_PRIORITY { LOW NORMAL @deadlock_var }

WHERE:

Low tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.

Normal tells SQL Server to use the default deadlock method.

@deadlock_var is a character variable specifying which deadlock method you want to use. Specify "3" for low, or "6" for normal.

This command is set a runtime for a specified user connection

SSIS debugging on 64

2008-05-16T13:30:00.000+02:00

Problem: When I debug my SSIS package (which imports an Excel-sheet into SQLServer) on a 64-bit server, I get this errors:
[Excel Source [9]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.".

Cause: There is no 64 bit driver for excel. The workaround is to run the package using the 32 bit version of the execution utility.

Solution: Project Properties->Degugging Section, I set Run64bit RunTime to False.

Rebuild Master --> back to RTM

2008-05-06T15:42:00.000+02:00

If you rebuild the master database in SQL Server 2005, it reverts to the RTM version, losing service packs and additonal patches.

The "strange" thing is that the server net libraries remain the original service pack versions, wich can be confusing.

sp_MSforeachtable and sp_MSforeachdb

2008-05-06T14:31:00.000+02:00

How simple can things be?

I just discovered these "undocumented" sp's. No more cursor creations for these kind of queries ;-). In combination with all those dbcc and dm_... statements and views (i.e. DBCC CLEANTABLE), a good thing to know. They both work in 2000 and 2005 (in 2005 there is no extra filtering on schema level :-s )

Examples:

USE AdventureWorks
go

EXEC sp_MSforeachtable 'select ''?'',count(*) from ?'

DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'

EXEC sp_MSforeachdb @command

Linked Server failure for IBM OLE DB Providers on SQLServer2005 X64 Cluster

2008-04-25T10:55:00.000+02:00

I've Created Linked servers to IBM AS400 machines using the IBMDA400 and IBMSQL400 OleDb providers. If I failover, or take off-line and bring back on-line the SQL Resource Group, I get this Error:

Msg 7340, Level 16, State 4, Line 1
Cannot create a column accessor for OLE DB provider "IBMDA400" for linked server "ATCOAIRC".

Solution:

- Browse in the Management Studio to the linked Server, request the properties, and close this window again everytime SQLServer Service starts ;-)

Or, if you don't want to do this manually - Duhhh...

- USE master;
GO

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE

EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
GO

CREATE PROC usp_enum_oledb_providers
AS

exec sp_enum_oledb_providers

GO

exec sp_procoption N'usp_enum_oledb_providers', 'startup', 'on'

Note: I didn't have this problem with SQLServer or Oracle Linked Servers.

IBM Ole DB error in SQLServer 2005 on x64

2008-04-11T10:50:00.000+02:00

Error:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "IBMDA400" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "IBMDA400" for linked server "(null)".

Or

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
--> An exception occured while executing a Transact statement or batch.
--> Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider "IBMDA400" for linked server.

Solution:

1) Make sure on your SQL Server 2005 x64 (64 bit) machine that "Allow inprocess" is checked for the linked server provider (IBMDASQL in my case).
--> In SQL Mgmt Studio, connect to the SQL Server Database Engine and go to Server Objects->Linked Server->Providers in the Object Explorer.
--> Right-click on IBMDASQL and select Properties. Check the "Allow inprocess" box in the Provider Options dialog that comes up.

2) Enable Ole Automation Procedures;

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

fn_my_permissions

2008-04-04T10:10:00.000+02:00

Returns a list of the permissions effectively granted to the principal on a securable.

Examples:

A. Listing effective permissions on the server
The following example returns a list of the effective permissions of the caller on the server.

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO

B. Listing effective permissions on the database
The following example returns a list of the effective permissions of the caller on the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

C. Listing effective permissions on a view
The following example returns a list of the effective permissions of the caller on the vIndividualCustomer view in the Sales schema of the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO

D. Listing effective permissions of another user
The following example returns a list of the effective permissions of database user Wanida on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on user Wanida.

EXECUTE AS USER = 'Wanida';
SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
GO

E. Listing effective permissions on a certificate
The following example returns a list of the effective permissions of the caller on a certificate named Shipping47 in the current database.

SELECT * FROM fn_my_permissions('Shipping47', 'CERTIFICATE');
GO

F. Listing effective permissions on an XML Schema Collection
The following example returns a list of the effective permissions of the caller on an XML Schema Collection named ProductDescriptionSchemaCollection in the AdventureWorks database.

USE AdventureWorks;
SELECT * FROM fn_my_permissions('ProductDescriptionSchemaCollection',
'XML SCHEMA COLLECTION');
GO

G. Listing effective permissions on a database user
The following example returns a list of the effective permissions of the caller on a user named MalikAr in the current database.

SELECT * FROM fn_my_permissions('MalikAr', 'USER');
GO

H. Listing effective permissions of another login
The following example returns a list of the effective permissions of SQL Server login WanidaBenshoof on the Employee table in the HumanResources schema of the AdventureWorks database. The caller requires IMPERSONATE permission on SQL Server login WanidaBenshoof.

EXECUTE AS LOGIN = 'WanidaBenshoof';
SELECT * FROM fn_my_permissions('AdventureWorks.HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
REVERT;
GO

Windows Vista and Office 2007 Compatibility

2008-04-04T09:47:00.000+02:00

SQL Server Service Pack 2 or greater is needed to be fully compatible with Windows Vista and Microsoft Office 2007.

Login "error States" in SQL Log

2008-03-28T01:34:00.000+01:00

States Description

2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

SQL Server Management Studio takes a long time to load, how to improve the load performance of this tool?

2008-03-27T12:24:00.000+01:00

In general the SQL Server Management Studio takes at least 10 to 15 seconds to load on a user machine. Sometimes the SSMS tool can take a long time to load when there are a lot of SQL Server instances, this problem may also occur on the new 64-bit SQL Server 2005 platform.
One resolution is to add a switch to the Management Studio shortcut as defined below. Access the below screen by going to Start > Programs > SQL Server 2005 program group > 'SQL Server Management Studio' program, right-clicking will get you the following screen by opening the General tab, then select shortcut tab to add 'nosplash' switch:

Also the user needs to tweak the Internet Explorer security settings as follows:
Uncheck two options in Internet Explorer's Advanced Security settings. Namely:
"Check for publisher's certificate revocation"
"Check for server certificate revocation".
Both these steps combined should improve the load time of SQL Server Management Studio.

How to debug T-SQL stored procedures in SQLServer 2005

2008-03-26T13:13:00.000+01:00

We will debug our procedure, not from Management Studio but from the Visual Studio 2005 (only Team Systems or Professional editions) development environment. I mention this because under SQL Server 2000 we are able to debug stored procedures using Query Analyzer. Perhaps debugging capabilities will be added to Management Studio in the future.
Before any debugging can take place, you first must create the store procedure. This can be done either by using Management Studio/New Query or by building the procedure graphically in Visual Studio 2005. Stored procedures can be created in Visual Studio by opening a new database project and using the Visual Studio Installed Templates.
Either way, once the procedure exists, you are ready to start debugging from Visual Studio 2005. When you start up Visual Studio for debugging purposes, you don't have to create a project. Instead, you can create a connection to your database from Server Explorer (You will have to provide your server name along with your login credentials and choose the AdventureWorks database.) Then you can drill down and open up the stored procedures folder. Next, right click on the procedure you wish to debug and choose Step Into Stored Procedure from the context menu. Then you are ready to start debugging!

Note: Before you can start "Remote debugging", you have to install "msvsmon.exe" from the visual studio CD or the sqlserver directory "C:\Program Files\Microsoft SQL Server\90\Shared\1033\rdbgsetup.exe".

Micorosft development team is looking to implement a better way within SQL Server 2008 and as of now 2005 is only way out with Visual Studio.

Moving msdb

2008-03-26T13:08:00.000+01:00

Moving MSDB is more complicated than other system databases and requires you to add trace flags and parameters to the SQL Server startup to detach msdb, then remove them and restart SQL Server to attach it:

Start SQL Server with -c, -m and -T3608 and then perform a detach. You would then remove these parameters before attaching MSDB.

Moving System Databases

2008-03-21T09:20:00.000+01:00

If you are moving system databases (master, msdb, model, tempdb), you would change the location of master by changing the startup parameters, bringing down the instance, and moving the master.mdf and master.ldf files.
Secondly you need to address the model database, because tempdb is reallocated every time the instance starts. This reallocation requires the existence of model to be successful.
The last one you have to re-address is the msdb, because msdb is used by the SQL Server agent and has no bearing on the stability of the database engine.

Hierarchyid Travesal (SQLServer2008)

2008-03-20T17:20:00.000+01:00

In which type of tree traversal would all nodes at a particular level below the root be traversed before those at lower levels?

Given a tree such as this:
1
/ \
2 3 4
/ \ / \ \
5 6 7 8 9

a traversal of nodes in 1-2-3-4-5 order would be a breadth-first traversal.