Wednesday, April 15, 2009
"Troubleshooting Performance Problems in SQL Server 2008"
Tuesday, March 31, 2009
SSRS configuring a rendering extension
Just add the following snippet to the Render section of the RSReportServer.config file and you'll have a new option to render your subscriptions.
<Extension Name="CSVSemicolon" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name Language="en-US">CSV (semicolon delimited)</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<FieldDelimiter>;</FieldDelimiter>
</DeviceInfo>
</Configuration>
</Extension>
Thursday, May 22, 2008
Arrivederci W.
Last week my favorite colleague - you know the one :) - left his company to launch his career into a whole new, interesting direction.
His old blog tells the story of his four years at our company as a database architect. It won't get updated anymore but many posts are still as relevant now as at the time they were posted - it's his long term memory after all. And there is the excellent blogroll of course.
The content of his new blog will surely treat topics differently (the host's always watching), but I hope he will continue to produce quality.
Enough said... success!
Wednesday, February 20, 2008
Dive into msdb: BackupSet & RestoreHistory
select top 1 b.backup_finish_date
from msdb..backupset b
join msdb..restorehistory r on b.backup_set_id = r.backup_set_id
where r.destination_database_name = 'MyDb'
order by r.restore_date desc
Tuesday, January 08, 2008
Transaction log files after reverting from snapshot
Lets illustrate this with an example, by creating a database SnapshotTest with 2 transaction log files, which will probably not be the same number as is the case for the model database. A snapshot of SnapshotTest with the name SnapshotTest_ss gets created.
Now for the interesting stuff: we revert the database from the snapshot and we investigate the transaction log files as we did earlier. Only the first transaction log file will be present for the SnapshotTest database but not with the same characteristics as before and not even the ones of model's transaction log. The second file is no longer part of the database. Is this desired behavior?
USE [master]
GO
CREATE DATABASE SnapshotTest
ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest.MDF')
LOG ON (NAME = SnapshotTestLog1, FILENAME = 'C:\TEMP\SnapshotTestLog1.LDF')
, (NAME = SnapshotTestLog2, FILENAME = 'C:\TEMP\SnapshotTestLog2.LDF')
GO
EXEC sp_helpdb SnapshotTest
GO
CREATE DATABASE SnapshotTest_ss ON (NAME = SnapshotTest, FILENAME = 'C:\TEMP\SnapshotTest_ss.MDF')
AS SNAPSHOT OF SnapshotTest
GO
RESTORE DATABASE SnapshotTest
FROM DATABASE_SNAPSHOT = 'SnapshotTest_ss'
GO
EXEC sp_helpdb SnapshotTest
GO
USE [master]
GO
DROP DATABASE SnapshotTest_ss
GO
DROP DATABASE SnapshotTest
Don't forget to clean up the database file that is left behind on the file system - it's no longer part of a database so it's not removed when you drop the database.
Wednesday, December 05, 2007
Focusing on SSIS
Yep, we like it just as much as we hate it at times. Some random stuff from the past weeks...
* SSIS is in a lot of ways much stricter than DTS, certainly when it comes to data transfer tasks. Encoding of files didn't bother us in the past, but now we have to be very aware of how we handle and manipulate our imports. A data conversion task gave me some hastle when it discovered a special character in an import which caused an error concerning the truncation of data for that field. Although the content wasn't exceeding the length of the field, it appeared that the task couldn't correctly determine the length, so I had to ignore the so-called truncation in the error handling options.
* On our main production cluster we had to deploy a package. We store our packages in Sql Server (msdb) at this time, but we just did not succeed in the import on the server (with all the SP's and CU's installed). It produced the following error:
Element not found. (Exception from HRESULT: 0x8002802B
(TYPE_E_ELEMENTNOTFOUND)) (Microsoft.SqlServer.DTSRuntimeWrap)
In the end, we had to import the package using the client (SSMS) from another server.
Tuesday, November 27, 2007
Database snapshot files
This can give some unexpected results when you query tables like sys.database_files for example. Instead of getting the set of files the snapshot exists of, you get the info that was originally in the referenced database, being the files of that database, not the snapshot. That information and much more is available through master.sys.master_files.
Tuesday, October 16, 2007
SSIS, 64-bit and a breakpoint
A quick search on the specialized forums returned some hits - mostly related to the Script task the package contained - but nothing really described the conditions I faced. The only difference I could think of was the fact that the acceptance environment was a 64-bit edition, unlike development. In such a case, the settings of the Script task are quite simple: the PrecompileScriptIntoBinaryCode flag should be on.
There was a non-active breakpoint however that caught my attention which I hadn't noticed before because the Script task was not part of my changes to the package and the tests had never halted at that break. After removing the breakpoint, the package effectively ran without a problem.
It's a shame we had to discover this bug the hard way, but it proves that an acceptance environment should stick as close to production as possible. In this case, it payed off.
Monday, October 15, 2007
SSIS's interaction with DTC
Investigating the blocking, I encountered some SPID -2 owned locks in sys.dm_tran_locks, thus blocking the data flow task from retrieving the necessary metadata from the database at the PreExecute event. I fiddled around with the setting of the TransactionOption and IsolationLevel of the different components, but nothing resolved my problem adequately. With the help of profiler however, I could determine that SSIS did not propagate the DTC transaction - started for the SQL task - to the Data Flow task.
The blocking can be resolved by either changing the ValidateExternalMetadata attribute of the OLE DB Destination or changing the TRUNCATE TABLE of the SQL task into a DELETE statement.
The reason why both of these solutions have such an impact remains unclear to me. It's true that each of these modifications affect the locks that are required by the package, but that doesn't explain the fact that the Data Flow task was not enlisted appropriately into MSDTC.
Thursday, September 13, 2007
sys.indexes and table valued functions
In the definition of the return value of a table valued function, you can define the primary key for that table and although it isn't an actual table - just a table variable created at run-time - metadata about the index is kept similar to any user table.
USE tempdb
GO
CREATE FUNCTION dbo.udf_TableValuedFunction ()
RETURNS @Table TABLE ( id int PRIMARY KEY , comment varchar(200) )
AS
BEGIN
INSERT @Table VALUES ( 1, 'comment 1' )
INSERT @Table VALUES ( 2, 'comment 2' )
RETURN
END
GO
SELECT *
FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id
WHERE o.name = 'udf_TableValuedFunction'
GO
DROP FUNCTION dbo.udf_TableValuedFunction
GO
Thursday, May 24, 2007
Unexpected dump
USE TempDB
GO
CREATE PROC proc_error
@Date DATETIME = blabla --no good!
AS
SELECT @Date + 1
GO
EXEC proc_error
GO
DROP PROC proc_error
GO
Msg 0, Level 19, State 0, Procedure proc_error, Line 4
SqlDumpExceptionHandler: Process 65 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Sunday, May 13, 2007
Plan Guides
Apparently, every morning, with a clean procedure cache, the procedure got called with certain parameters which resulted in a not very intuitive, but good plan with a cost of 3 reads. Subsequent calls with different parameters reused the cached plan, but that resulted in > 8000 logical reads and quite some CPU usage. A more intuitive plan resulting from typical parameters had a constant cost of about 8 logical reads for both types of parameters, which can give a really big performance boost, given the fact that the procedure gets called more than 40.000 times a day and delays a vital business process.
Because I'm not allowed to modify anything of the stored procedure (yep, it's CRM), I had to use a plan guide to 'guide' the engine to the more performant plan. There are several options: specifying the whole plan, add the parameters for the compilation, force recompile... I chose the parameter hint.
--solution, add plan guide
exec sp_create_plan_guide
@name = N'Plan for procedure p_GrantInheritedAccess UPDATE'
, @stmt = N'UPDATE poa1
SET InheritedAccessRightsMask = poa1.InheritedAccessRightsMask poa2.AccessRightsMask poa2.InheritedAccessRightsMask 0x08000000
FROM PrincipalObjectAccess poa1 JOIN PrincipalObjectAccess poa2 ON (poa1.PrincipalId = poa2.PrincipalId)
WHERE poa1.ObjectId = @ReferencingObjectId
AND poa1.ObjectTypeCode = @ReferencingObjectTypeCode
AND poa2.ObjectId = @ReferencedObjectId
AND poa2.ObjectTypeCode = @ReferencedObjectTypeCode'
, @type = N'OBJECT'
, @module_or_batch = N'p_GrantInheritedAccess'
, @params = NULL
, @hints = N'OPTION(OPTIMIZE FOR(@ReferencingObjectId=''D4F70FD5-41FA-DB11-9668-0011258C53FD'',@ReferencingObjectTypeCode=4200,@ReferencedObjectId=''2052793D-41FA-DB11-9668-0011258C53FD'',@ReferencedObjectTypeCode=4200))'
SELECT * FROM sys.plan_guides
The result of this simple operation is a clean and transparent solution. I haven't put it into production though, because it stays a last resort, but it's always good to know your options.
Wednesday, May 09, 2007
MS CRM, from a database perspective
We started the cleanup with resolving some major issues in the custom code. That code was written by rather inexperienced developers with little supervision on their creations. That resulted in some very interesting behavior on our production environment from time to time, at least if you're in for a challenge. The problems can be roughly categorized into two groups: locking issues and suboptimal plan usage, mostly due to wrong datatype usage.
After the resolution of the most urgent issues on the customization, I got the chance to take a look on the actual MSCRM database. And it isn't always a pretty sight. Sure, the system of a controlling Metabase and a generic customizable CRM database are pretty neat, but the statements that I see passing in the profiler traces are often quite unmanageable. In exchange, the application itself offers very flexible functionality, but it doesn't always seem to scale that well to the data volumes we are handling in our company.
Well, working with MS CRM 3.0 is proving to be an enriching experience. You actually shouldn't have to look at the database and you should definitely not temper with it, but Sql Server 2005 offers very interesting features to pinpoint the culprits and to help the engine a little in the right direction. More to come in hopefully a little more technical posts ;-).
Thursday, January 25, 2007
SSIS BypassPrepare
Yesterday, we stumbled into an annoying bug of the RTM version of SQL Server 2005. A new version of a previous project of mine was being released with a modified SSIS package. One of the modifications consists of the usage of a mapped input parameter in an Execute SQL Task. It requires the BypassPrepare flag of that task to be changed to True to make this construct work. It was succesfully tested in development, acceptance and then, in production, we suddenly got the following error.
Executing the query "…" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This error lists almost everything that can possibly go wrong with an Execute SQL Task without giving any indication what actually went wrong. Sigh.
At first I thought that the problem was caused by the fact that our production environment didn't had SP1 and the necessary hotfixes like it should have. That's an essential factor indeed, but there was more to the problem. The trouble with the RTM version is that it doesn’t take this BypassPrepare into account. Whether you set this flag to true or false in the development environment, if you look at the actual xml that represents the package, you won't find a trace of this setting. This was fixed in SP1 (as correctly answered on the forums).
What I didn't know was that our operational DBA did export and import the package with his local client (SSMS) on which he didn't have SP1 either. Similar to the behavior in Visual Studio, the setting gets lost with SSMS in RTM version. After a quick install and verification, everything worked smoothly.
In the upcoming SP2 it is announced that the default of this setting will be changed to True, meaning that SSIS won't prepare that statement unless you explicitly specify it do so. It could have saved us some trouble. Valuable lesson learned is to keep all environments, and also the clients, always up to date.
Thursday, December 21, 2006
Difference between @@IDENTITY and SCOPE_IDENTITY()
It states :
It took me a minute to find a good example to illustrate the difference between the two, but with a trigger I created the following example:@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
USE TempDB
GO
CREATE TABLE tst
( a int identity(1,1), s varchar(10))
GO
CREATE TABLE tst2
( a int identity(1000,1), s varchar(10))
GO
CREATE TRIGGER dbo.trgTst
ON tst
AFTER INSERT
AS INSERT tst2 SELECT inserted.s FROM inserted
GO
INSERT tst VALUES('a')
SELECT
@@IDENTITY AS [@@IDENTITY],
SCOPE_IDENTITY() AS [SCOPE_IDENTITY()]
GO
DROP TABLE tst2
DROP TABLE tst
SCOPE_IDENTITY() will give you the value of tst.$IDENTITY, ignoring the identity value that is generated for table tst2 with the trigger after the insert into tst. @@IDENTITY will give you that value from tst2.$IDENTITY.
So the function we actually need in our case is not @@IDENTITY but SCOPE_IDENTITY().
Wednesday, November 22, 2006
$ROWGUID
Anyway, when I was looking at some of the generic legacy code to identify the ROWGUID of a table, I remembered something about $ROWGUID. This automatically returns the ROWGUIDCOL of that table. Sadly, it's new in SQL Server 2005 and because we haven't had the chance to migrate that database, we still need to use some looping to investigate the COLUMNPROPERTY to finally get the name of the column that can be used in dynamic SQL to mimic the behavior of $ROWGUID:
USE TempDB
GO
CREATE TABLE SomeTable
(
ID uniqueidentifier ROWGUIDCOL
)
GO
SELECT $ROWGUID FROM SomeTable
GO
DROP TABLE SomeTable
Thursday, October 19, 2006
SQL Server 2005 System Views Map
Tuesday, September 12, 2006
Be creative with BCP
When using FOR XML EXPLICIT, you don't get that many options to export the result to a file because of the special nature of that result. But with BCP, everything is possible - at least I thought so. Because of the complexity of the export, some data had to be staged temporarily in the stored procedure that retrieved the data and formatted the XML. And that's where the trouble started.
With the code below you simulate the core of the problem - which isn't FOR XML by the way.
USE TempDB
GO
CREATE PROC usp_bcp_test
AS
SET NOCOUNT ON
IF 1=0
BEGIN
DECLARE @Tbl TABLE (Id INT)
INSERT @Tbl VALUES (1)
END
SELECT 1 as ColA, 'test' as ColB
GO
DECLARE @Stmt VARCHAR(1024)
SET @Stmt = 'BCP "EXEC TempDB.dbo.usp_bcp_test" QUERYOUT C:\TEMP\tst.txt -c -S ' + @@SERVERNAME + ' -T'
EXEC master.dbo.xp_cmdshell @Stmt
GO
DROP PROC usp_bcp_test
GO
I try to use the output of the stored procedure as the 'query' for BCP, but this fails because of the INSERT-statement in the stored procedure, even though it's unreachable code. BCP gives the following feedback:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column
NULL
Using Profiler, I discovered that BCP issued the following statement to determine the resultset it can expect.
SET FMTONLY ON EXEC TempDB.dbo.usp_bcp_test SET FMTONLY OFF
In the messages pane of SSMS you can see the following:
(0 row(s) affected)
(0 row(s) affected)
Which explains the fact that BCP complains about the stored proc: it expects the INSERT statement to give a columnlist for its resultset, which it doesn't have of course. Moving the SELECT clause to the begin of the stored proc makes this construct work because BCP only looks at the first resultset.
The solution to this problem is straightforward: split the retrieval and the formatting over multiple stored procs so that the stored proc used for the BCP only contains a simple SELECT-statement.
Nice little detail: I was only able to simulate this behavior in SQL Server 2005, not with SQL Server 2000.
Tuesday, August 29, 2006
Plan cache gets cleared
EXEC sp_configure 'max degree of parallelism', 0
RECONFIGURE
Tuesday, August 08, 2006
Behavior of Logins/sec
Nothing wrong with the connection pooling luckily, but it seems that the behavior of the performance counter has changed between versions. It seems to indicate not only the actual logins, but also the reuse of connections because of connection pooling. A colleague put the matter on a forum for confirmation. Tbc?