Archive
How to find out what is causing page splits?
Client was noticing high number of Page Splits/sec in relation to Batch Request/sec; however could not figure out why. They were not have performance issue but still was curious what was generating it?
So how do we track it? Well short of the answer is there is no easy way to do this. Even though perfmon has ability to tell you page splits are taking place by Page Splits/Sec in SQLServer: Access Methods; it cannot tell you where.
We can use the fn_dblog function to read the transaction log file in SQL Server 2000/2005 and in SQL Server 2008 we can rely on extended events.
SELECT Operation
, AllocUnitName
, COUNT(*) AS NumberofIncidents
FROM sys.fn_dblog(null, null)
WHERE Operation = N'LOP_DELETE_SPLIT'
GROUP BY Operation, AllocUnitName
Example from #1.
I am not going to repeat the work done by others here, the code or examples are from reference links below if you need additional details.
- Daniel Adeniji’s – Learning in the Open. Microsoft – SQL Server – Page Splits. Link.
- Microsoft Connect. SQL Server Extended Events Page/Splits Event Additions. Link.
- SQL Server Pro. Evaluating the Page Splits/sec Value. Link.
- SQLBlog.Com. Michael Zilberstein (Hopefully soon to be a SQL MVP ;-)). Monitoring page splits with Extended Events. Link.
Edit Notes
- March 30, 2012: I had it wrong that Michael is not a SQL MVP yet, so here to hoping he gets MVP :). Thanks for correction mate.
OSQL and Code Page Settings
Credit: Carl B. (SQL Server Central, Link)
In SQL Server 2000, when using OSQL it is difficult to work with import files that are in different encoding. Because when importing OSQL tends to take the local code page settings and applies it to input file. Only way I found to get around that issue was to save the file as unicode and then import it. Which works; but when working on large import files this turns out to be a headach.
Carl found following registery settings can be changed to achive same affect; as recommend by him becareful about changing them though :)>. I would change them back after doing the work.
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\]
-> OEMCP: Changing its value from 850 to 1252.
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\]
-> 850: Changing it’s value from c_850.nls to c_1252.nls.
Interseting bit of info .. as Carl warns use it carefully, we wouldn’t want something else to blow up :).
Error 14274: Cannot add, update, or delete a job (or its steps or schedule) that originated from an MSX server.
You can get this error when trying to modify the properties of a Job after the SQL Server has been renamed or cloned into another computer. When we create a job on SQL Server it creates an entry in sysjobs table logging the job and server originated from. We can fix this issue by updating the sysjobs table:
USE [msdb]
GO
UPDATE sysjobs
SET originating_server = 'NewServerName'
GOIn addition to jobs not working now the @@ServerName variable can also return invalid information, old server name. Because the @@ServerName gets the server information from the sysservers system table. We can fix this issue with following script:
USE [master]
GO
sp_dropserver 'OldServerName'
GO
sp_addserver 'NewServerName', 'local'
GOYou will have to restart the SQL Server to see the new changes.
Granting Execute Permissions
Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.
EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO
EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO
SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
INTO #NewPermissions
FROM sysobjects
WHERE xtype = 'P'
AND ( name LIKE 'sp[_]%' OR
name LIKE 'up[_]%')
ORDER BY name
GO
DECLARE @SQLStatement VARCHAR(512)
DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
FROM #NewPermissions
OPEN SPPermissions
FETCH NEXT
FROM SPPermissions
INTO @SQLStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing: ' + @SQLStatement
EXEC (@SQLStatement)
FETCH NEXT
FROM SPPermissions
INTO @SQLStatement
END
CLOSE SPPermissions
DEALLOCATE SPPermissions
GO Status Column in Sysusers
I have encountered issues after adding logins to the SQL Server 2000 databases where even though the servers show up in the user list they still cannot access the database. It turns out I only granted the user permissions to the database and not actually added them; so it failed. In doing that found following states:
| State | Description |
|
4 |
User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT Group). |
|
6 |
User has permissions to the database (objects) and user belong to the database (Windows NT Group). |
|
12 |
User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT User). |
|
14 |
User has permissions to the database (objects) and user belong to the database (Windows NT User). |
I wouldn’t recommend making any coding on this; but it is a good reference. And I don’t know if these hold true for SQL Server 2005 or 2008 yet.
