Transfer logins

Recreate SQL Logins:

Imagine a scenario where you need to uninstall and reinstall
sql server. In such a case you may need to recreate the logins. Assume that
you dont have the password for the sql logins that are to be recreated.
In such a case we have a option of scripting all the logins ( or any )
into a SQL script. SQL Script on execution on the new server will
recreate the logins previously created with the SAME password.
The best thing is the password is not exposed in the script.

This script also works to transfer logins from sql 7 to sql 2000 ,
7 to 2005 and 2000 to 2005.This script and the method is described in detail in Microsoft website.But as this is my blog let me give a brief description of the method.

1) First on the source( old server) create the sps sp_hexadecimal,sp_help_rev_login,
sp_help_revlogin_2000_to_2005( if you are transfering from 2k to 2k5 )
on the master database.

2) On the Query analyzer run

EXEC sp_help_rev_login 'Loginname' --for a single login name
EXEC sp_help_rev_login --for all logins

3) Output will be 'Create Login ...'. Save the output on a .sql file.
Execute the same on the destination server to have the logins created with the same password. Simple. Just a small note.
Have the query analyzer result window in text mode ( ctrl + t) so that
the formating of the output script is not distrubed.

Rest of the details ( and the one repeated here :) ) are given clearly
in microsoft website. Take a look.


For SQL Server 7 and 2000 and for transferring logins from 7 to 2k5 and 2000 to 2005

http://support.microsoft.com/kb/246133

For transferring logins between Sql Server 2005 servers.

http://support.microsoft.com/kb/918992

Fill Factor - 3

As already mentioned this post will attempt to answer the follwoing question

> While deciding fill factor, What options are we left with when we don't have a good idea of how many inserts are happening?

There are two DMVs which can help us in such a case.

1) sys.dm_db_index_operational_stats
2) sys.dm_db_index_usage_stats

Let us take up the first one - sys.dm_db_index_operational_stats.
Index_operational_stats provides very very valubale info like

> Leaf_insert_count/Leaf_delete_count/Leaf_update_count
> Leaf_allocation_count

As the name suggests the columns Leaf_insert_count,Leaf_delete_count,Leaf_update_count provide
the number of rows inserted,updated,deleted on the index. So when you want to check for the important
table then query for the clustered index of the table as clustered index's leaf level represents
the actual table. It can be done by

SELECT *
FROM sys.dm_db_index_operational_stats
( db_id() , object_id('Supplier_Product_Customer'), 1 , NULL )
WHERE Index_id = 1

Just a small note here. Sometimes the leaf_delete_count can be zero( or less than actual number of deleted rows ) even if deletion had occur ed. In that case one can check leaf_ghost_count which would represent the number of deleted rows. The deleted rows are not physically moved but are marked as deleted ie logical deletion.SqlServer periodically physically removes deleted rows from the disk but not immediately after the deletion.leaf_ghost_count also has number greater than zero when transactions are rolled back.

Leaf_allocation_count indicates the total number of new pages that were allocated to the table.This can relate to the Page splits, if most of your inserts are completely randomized and very little is happening at the tail of the index.

There are quite a few other useful columns row_lock_count,row_lock_wait_in_ms but I will leave them for you to explore.

So, we have all the info that's needed to compute the fill factor. Apply the
Leaf_insert_count,Leaf_delete_count,Leaf_update_count
on the method specified here then you should get your ideal fill factor..right.. But not so easy..Like all wonderful things in life, this one also comes with a drawback. One major drawback is that the details about the table are retained on the
DMV only till the meta data of the table remains in cache. Once the table's meta data moves out of cache, the row containing the useful info is removed from DMV and the values are reset to zero. But how long it will be retained depends on how well your table is getting used and how well other tables are getting used as well.

So, one option you have is to dump the DMV's result periodically into a table and then later use it for analysis. As querying this DMV for a single ( or few ) table doesn't acquire any locks on the actual table,and it is not a resource consuming exercises. But, the analysis can be very tedious because one needs to check whether the table's meta data moved out of the cache or not by comparing the result recorded previous time.For ex: if your number of inserts recorded at 10 AM is less than number of inserts recorded at 9:45 AM then its likely that the table has moved out of the cache between 9:45 and 10. So, analysis can be painful but its the best option available.

Then the Second option we have is sys.dm_db_index_usage_stats .Column of interest is user_updates.There are other interesting columns like user_seeks, user_scans, last_user_seek, last_user_scans but now the one that's needed is user_updates. User_updates column indicates the number of changes that occur ed on the index. In other words,User_updates number represents the total number of insert,update and delete queries that were fired against the table. Point to note
is, its total number of queries and not rows which kind of dents our cause. But it can be useful if you are aware of your queries say something like all your inserts/updates/deletes affect one row or 'x' rows at a time.The good thing is that the DMV retains the value till the next restart of the SQLServer.Still its not a accurate method of measuring as its a cumulative sum of update, delete ,insert queries and doesn't provide the breakup.

After estimating the inserts,updates and deletes and setting the initial fill factor one needs to monitor fragmentation numbers, page count( for checking number of page growth ), page splits and if possible data distribution. Data distribution would help one to check whether the data distribution is skewed and up to what extent.Skewed data can definitely disturb your calculations and make indexes perform
below par. 2k5 doesn't provide a perfect solution for skewed data distribution and its problems.But 2k8 to a certain extent does with Filtered indexes. Will write a piece on that soon.

At last I am kind of done with the series on Fill factors and fragmentation.
Will stay away from this one at least for the next few posts :)

Identifying Page Splits

I ended my last post on fill factors with following question..

What are the other things that need to be monitored after setting a fill factor?

How do we decide on the fill factor when we are not too sure about the random insert frequency?

But before answering that question, I felt its essential to write about the technique to detect the occurrence of high page splits and directly finding the object having maximum page splits. A table with a high fragmentation value indicates a very very likely chance of it having high page splits. This post will talk about the method to find the object that experienced high page splits within a certain period. I wouldn’t call this post a digression from last topic, but it doesn’t exactly answer the questions with which I ended my last post. But its important for one to know to detect high page splits occurrence this way other than checking it thro fragmentation numbers.


So Whats the starting point..As always in any performance tuning exercise the starting point is Perfmon Counters.I will do a full length post on Perfmon counters later ( may even be a series of posts ) but for the time being I will give a brief 2 line intro of Perfmon, just in case if you are not aware of it. Perfmon is a utility provided along with Microsoft windows, which helps you to monitor various system parameters like CPU/Memory/IO usage/Queue length/ and SQL Server parameters(counters) like Lock waits, Number of transactions,Number of Deadlocks, Number of User connections, Buffer cache hit ratio to name a very few. You can find the list of SQL Server related counters using the DMV/view

Select * from sys.dm_os_performance_counters : if on 2k5
Select * from dbo.sysperfinfo : if you are on 2k

One such counter called Pagespilts/sec under 'SQLServer:Access Methods' object is useful in finding the page splits happening on your server. If you average more than 10 with around( 50-100 active users ) during the day then I really start to worry and perhaps look at options to bring it down. Note the number provided is at server level and its a cumulative figure across all tables and databases. So if you have multiple databases working for multiple applications and each one with different range of users then you are better off monitoring regularly to identify your peak and off peak numbers.

As mentioned the Perfmon counter tells us page splits/sec across server and not which database or table. When a high Page split/sec is noticed the next step would be to find table is most likely responsible for it. For that there is cheeky way of
doing it. That is reading the SQL Server transaction log thro an undocumented function. The function fn_dblog can read a SQL Servers T-Log. It can be used in the following way.

Select * from ::fn_dblog(null,null)

Reading the transaction from the result thrown is bit difficult and cryptic as the function is undocumented and known only to microsoft. But we can make a few intelligent guesses using the columns and values displayed. The following queries can help you identify the object that had high page splits

For 2k:

Select [Object Name], [Index Name], count(1)
from ::fn_dblog(null, null)
Where Operation = N'LOP_DELETE_SPLIT'
Group By [Object Name], [Index Name]
Order By Count(1) desc

The object name would be object you were looking for. Count here refers to the number of rows that were moved because of a split.


For2k5:

Select COUNT(1) AS NumberOfSplits, AllocUnitName , Context
From fn_dblog(NULL,NULL)
Where operation = 'LOP_DELETE_SPLIT'
Group By AllocUnitName, Context
Order by NumberOfSplits desc

This one tells us the number of splits, where AllocUnitName refers to the object name.With these two commands one can find which object is experiencing high page splits. As these commands rely on Transaction log its better you record these values before running Backups against your database or executing a Backup log with Truncate_only command. Another issue with this one as its undocumented and relies on transaction log file structure it occasionally throws some weird results. But if two to three trials confirm the object name then it should be one object that you were searching for.

So, now we have identified our object with high page splits. But, one can ask why would one need to do this as the Fragmentation Script can detect table with high fragmentation. Consider the following scenario. A table starts after the maintenance with 95% fill factor. Undergoes heavy page splits and becomes 50% full at the middle of maintenance cycle. After that having obtained many inserts it can again become say 90% full before the next maintenance window. So when you record the table fragmentation using the percentage used in fragmentation script it would tell you its 90% full which can mislead a DBA to think that fill factor is working.Fill factor is not doing the best as for more than half the time the table is only half or little over half full. In such a scenario though the internal fragmentation doesn’t indicate the correct picture. Logical fragmentation in the Fragmentation Script should provide hints on problem happening. But anyways, its better to have direct method to monitor high occurrence of page splits as shown above. So, its always important to measure fragmentation levels ( using my script please :) ), pagesplit/sec counter and number of pages on each table.Number of pages on each table can help in identifying the growth of the table/ increase in page count since last maintenance which is very very likely to be due to page splits. When one correlates these parameters, one can clearly confirm the object is involved in high page splits.

So, after confirming the object experiencing high page splits, one can get into the next step of estimating how many random inserts are happening in the table, without having any knowledge about the application.

Check Database Fragmentation -SQL 2000

Again as requested, I have come up with a script to check
fragmentation for SQL 2000. This one as expected works using DBCC
Showcontig.Fairly simple script. Let us take a look

CREATE TABLE #fraglist

(

ObjectName CHAR(255),

ObjectId INT ,

IndexName CHAR(255),

IndexId INT ,

Lvl INT ,

CountPages INT ,

CountRows INT ,

MinRecSize INT ,

MaxRecSize INT ,

AvgRecSize INT ,

ForRecCount INT ,

Extents INT ,

ExtentSwitches INT ,

AvgFreeBytes INT ,

AvgPageDensity INT ,

ScanDensity DECIMAL ,

BestCount INT ,

ActualCount INT ,

LogicalFrag DECIMAL ,

ExtentFrag DECIMAL

);

INSERT

INTO #fraglist EXEC

(

'DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES'

)

SELECT SUM((Countpages * 8 ))/1024.0 AS Available_Space_in_MB,

CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 AS Space_Used_in_MB ,

(CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 )/( SUM((Countpages * 8 ))/1024.0 ) * 100 AS Percentage_Used ,

( SUM(

CASE

WHEN indexid IN (0,255)

THEN 0

ELSE CountPages * LogicalFrag

END /100.0) / SUM(CountPages ) * 100) [Total_Logical_Fragmentation_in_%],

( SUM(

CASE

WHEN indexid IN (0,255)

THEN 0

ELSE Extents * ExtentFrag

END /100.0)/ SUM(Extents ) * 100) AS [Total_Extent_Fragmentation_in_%],

(CONVERT(DECIMAL,SUM(BestCount))/CONVERT(DECIMAL,SUM(ActualCount))) * 100 AS Scan_Density

FROM #fraglist

WHERE ActualCount!=0

AND Extents !=0

AND CountPages !=0

Let me run thro this script briefly. A temporary table
#fraglist is created.

The Showcontig results for each table in the database are dumped
into temp table using the command

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES.

A query to give a consolidated report at database level is ran
against the temp table.

A quick run thro results provided. Most of columns have the same
definition as provided in my script for SQL 2005.But, there are few differences as well.

Available_Space_used_in_MB: Available_Space_used_in_MB is the sum of the space allocated to each table/index in the database.Available_Space_used_in_MB has
the same definition as the one in SQL 2005 script but the difference is that in 2k Script I have
expressed it in MB, but in 2k5 script I have expressed it in pages.Also, realise the difference between Available_Space_used_in_MB and the actual database size. The actual database size would include the space occupied by dropped objects and deleted objects. Unless you shrink your database, the database doesnt release the space occupied by dropped objects or deleted
rows. So, total database size would be always greater than the Available_Space_used_in_MB value provided by this query.

Space_Used_in_Mb: Space_Used_in_Mb is the sum of space actually used by each table.

Percentage Used: Percentage of Available_Space_used_in_MB used. Expected to be around 90%+ or close to the fillfactors set for your bigger tables.

Total_Logical_Fragmentation_in_%: Total_Logical_Fragmentation_in_%
percentage of out of order pages. As mentioned in my previous post less than 10% is
acceptable. Logical fragmentation applies only for indexed tables. and for not for heaps. If
logical fragmentation is greater than 30% go for a Rebuild or if its between 10 to 30% then DBCC INDEXDEFRAG(Databasename,tablename) would do.

Total_Extent_Framentation_in_%:
Total_Extent_Framentation_in_% refers to the percentage of extents that are out of order. Extent Fragmentation displayed by Showcontig doesnt apply for heaps.Please note the difference between script used for SQL 2k5 and 2k. In 2k5, sys.Dm_db_index_physical_stats
shows Extent fragmentation values only for heaps and not for indexed tables. But in Showcontig used in SQL 2000, Extent fragmentation applies only for indexed tables and not for
heaps at all. Again as mentioned before acceptable value for extent fragmentation is less than 10%.

Scan Density: Scan density is the ratio of minimum number of extents required to hold the table ( page count/8) and the number of extent switches required by the DBCC command to
scan the table, expressed in %. Extent switches basically represent the number of jumps made from one extent to another while accessing the entire table. Scan density Percentage is
common for both indexed tables and heaps. So, Scan density percentage can be used to measure the fragmentation percentage on heaps as Extent and logical fragmentation only apply for
indexed tables. Ideally, Scan density is expected to be in excess of 90%. I have noticed that the scan density provided by Showcontig is not always the exact ratio of minimum number of extents/Extent Switches as mentioned in BOL. But definitely a useful parameter to get an idea of fragmentation levels in the tables

Analyze the parameters specified above and if high values are obtained, then execute the query below on the same temp table to get fragmentation values at table level.

SELECT Objectname ,
SUM((Countpages * 8 ))/1024.0 AS Available_Space_in_MB,
CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 AS Space_Used_in_MB ,
(CONVERT(DECIMAL,SUM((Countpages * 8 * 1024.0 ) - ( Countpages * AvgFreeBytes) ) /1024.0)/1024.0 )/( SUM((Countpages * 8 ))/1024.0 ) * 100 AS Percentage_Used ,
( SUM(
CASE
WHEN indexid IN (0,255)
THEN 0
ELSE CONVERT( DECIMAL,CountPages * LogicalFrag)
END /100.0) / SUM(CONVERT(DECIMAL,CountPages) ) * 100.0) [Total_Logical_Fragmentation_in_%], ( SUM(
CASE
WHEN indexid IN (0,255)
THEN 0
ELSE CONVERT( DECIMAL,Extents * ExtentFrag)
END /100.0)/ SUM(CONVERT(DECIMAL,Extents) ) * 100.0) AS [Total_Extent_Fragmentation_in_%],
(CONVERT(DECIMAL,SUM(BestCount)) /CONVERT(DECIMAL,SUM(ActualCount))) * 100.0 AS Scan_Density
FROM #fraglist
WHERE ActualCount!=0
AND Extents !=0
AND CountPages !=0
GROUP BY objectname
--DROP TABLE #fraglist

As mentioned earlier the #fraglist table contains the entire resultset of showcontig and has details like

Row count, Min/Max and Avg Row size, AvgFreeBytes etc. Refer to them while doing a detailed analysis.

Though this script is 2k5 and 2000 compatible, DMVs are definitley better. The major drawback with ShowContig is that it is not an online operation which implies that users can only select and not insert/update/delete when showcontig is executed on a particular table. Also noticed that DMVs are faster as well.

But anyways, this script would be extremly useful if you can spare a few minutes on your maintainence window.

Fill Factor- Part II

Again as another reader requested, I decided to write a fill factor part-2, mainly concentrating on how to set a fill factor. Setting a fill factor is tricky thingy with quite a few factors involved. In this post, let me narrate them and Perhaps come up with a technique to figure out the fill factor percentage to be set.

Setting a fill factor varies from table to table depending upon its usage. Assume that your table has a clustered index on auto identity column then your fill factor can be happily set to 100% as there is almost no chance of having a insert in between as all inserts would happen at the tail of the index. So, it becomes diffcult ( and interesting as well ) only when you have random inserted values on your clustered index key. Let us see how can one go about setting a fill factor in such a case.

The main idea behind setting a fill factor is to keep page splits as minimum as possible. Reducing Page splits can be done by reserving some space for the random inserts that are expected to happen, which is achieved by setting the appropriate fill factor. For setting a fill factor one needs to gather the following details.

1) How frequently one can rebuild the index? (Weekly once/ Daily /). Let me call this frequency to be maintenance window.Maintenance window detail is needed because at every Index rebuild we can get our tables rearranged as per the fill factor.

2) Estimate of how many inserts/updates/deletes is expected with in a maintainence window. Most important is how many random inserts.Other than inserts, updates to varchar,nvarchar columns can cause the pages to split. Its bit difficult at times to find these numbers. But it does matter a lot.

3) Find the Average row size and number of pages in your table( DBCC Showcontig and
sys.dm_db_index_physical_stats can help ) .

After finding the above-mentioned details, one needs to find the Average Random rows to be inserted per page. This can be found by dividing the total number of pages ( showcontig ) by the random inserts estimated. Note that by dividng so we assume that all the random inserts are evenly distributed across the table which in reality may not be the case.So we can do a bit of adjustment to cover the same. Dividing so provides us Random inserts per page.

Total Number of pages/ Estimated random inserts = Random inserts per page.

After that we need to find how much of space needs to be reserved at each page for accomodating random inserts.That would be

Random inserts per page * Avg Row size = Size to be reserved for random inserts.

Fill factor would be

(( 8196 - 96 - Size to be Reserved for random inserts ) / 8196 ) * 100 +/- 5%

96 bytes is the size of page header and hence the adjustment. 5% approximation is meant for the likely skewed distribution across the table for random inserts, varchar updates and deletes if any. Its kind of unusual to have a fill factor below 65%. If your calculations reflect less than 65% then perhaps you may look at shorter maintenance windows(if possible of course) as an option.

After setting the fill factor the responsibility of the DBA is not over. One needs to monitor the Avg page Density and Scan density values ( or Logical fragmentation and Percentage used in my script ) regularly and note the fluctuations and act accordingly.

The story is only half complete. There are more things to be written on the same topic. So will be doing part-3. On Part-3 we would be looking at couple of things.
1) Few more things that are to be monitored after setting the fill factor initially.
2) More often than not one doesnt have a clear idea on number of random inserts expected. In such case what are options we are left with.
So, Watch out for Part-3.

Undocumented sps - Part II

As there was a comment on my last post for more extended undocumented SPs, I thought I will do a part - II for it. I haven’t found a serious necessity till date to use the extended sps that have been listed out here.
But anyways, let me put them here as requested.

1) sp_helpextendedproc :

Lists all extended system procedures including the undocumented ones. The system procedures include extended sps which use DLL ( like xp_cmdshell) and other system procedures which are used internally by SQL Server( whose source code is not available for us to see ). So if you are looking for more extended sps than that are listed here then you may very well run sp_helpextendedproc and it may help you to find the one you wanted.

Example :
EXEC master..sp_helpextendedproc

2) xp_getfiledetails:
Provides file details like size( in bytes ), Creation date and time, Last accessed date and time.xp_getfiledetails works only on SQL 2k and it is removed in SQL 2k5.

Example:

EXEC master..xp_getfiledetails 'C:\test.txt'

3) xp_subdirs:

Provides the list of subdirectories present in the specified directory.Does the search up to only one level.
Example:
EXEC master..xp_subdirs 'C:'

4) xp_dirtree:
Provides the list of subdirectories for the specified directory. Unlike xp_subdirs doesnt limit itself to 1 level and traverses the complete depth.

Example:

EXEC master..xp_dirtree 'C:'

5) xp_getnetname:
Returns the Servers network name. Note that it is different from Serverproperty('Servername') as Serverproperty returns the name of the SQLServer running on the Server. But xp_getnetname returns the network name of the machine running the SQLServer.

Example:
EXEC master..xp_getnetname

6) xp_enum_oledb_providers :
Provides the list of OLEDB Providers.

7) xp_enumdsn: Used to list all DSNs that are created on SQLServer. Works only with SQL 2000.

8) xp_enumerrorlogs : Lists the number of SQL error log files that are present in SQL Server.

9) xp_enumgroups : Lists the number of Windows groups that are present in the SQL Server Machine. This command lists all the groups present that are present in the machine and not just the ones that have access to SQL Server.

10) xp_fileexist - Can be used to find whether a file/directory exists. Indicates whether its a directory/file and whether it has a parent directory.

There are few more like xp_regdeletekey,xp_regdeletevalue,XP_regread, XP_regWrite to meddle with the registry.I seriously dont expect myself to meddle with the registry and would dump it on the System Admin folks to do that :)
So, I am not getting into the details of it.

Useful Undocumented Extended Sps

First post after launching my blog..

Now its lot more exciting ( and a bit scary as well :) ) to post..

Thanks to all the people for reading my blog and please keep reading :)

There are quite a few undocumented extended sps in SQL 2k/2k5. Just thought of discussing couple of useful ones..

EXEC MASTER..Xp_fixeddrives

Returns the free space on all hard disks on the Server running SQL Server.

If you are always running short of space then this one can be handy.

EXEC MASTER..Xp_servicecontrol 'Querystate' , 'SQLAgent'

Returns the Current state of the SQLAgent ie One can check whether the SQL Agent is running or stopped.XP_Servicecontrol requires Sysadmin rights as one can start or stop a sqlagent using it.

EXEC MASTER..Xp_servicecontrol 'Start' , 'SQLAgent' -- to start

EXEC MASTER..Xp_servicecontrol 'Stop' , 'SQLAgent' -- to stop

The second parameter in the above command is the SQL Agent's service name.

For a default instance it is 'SQLAgent'. For a named instance it would differ.

SQLAgent service name can be checked by going to Start->;Run->;Services.msc and identifying Sql agent service. Normally for a named instanced it is named in the following way.

The name would be SQLAgent+$+instancename.

For ex: If the instance name is Raj then its 'SQLAgent$Raj'.

But always you are better off checking it on the actual server.

Also note that Servicecontrol can check the state of other services running on the server.For ex:

EXEC MASTER..Xp_servicecontrol 'Querystate' , 'IISAdmin'

So just be careful while using it or while assigning permissions to it.

Also, this command igoners spaces in between. ie.. If service name is

IIS Admin this one takes it as IISAdmin.

Its undocumented..So no guarentees that it will work the same way after a patch installation etc..So these commands can be used for occasional checking but not recommended to be a part of your application code.So just bear in mind..

These two commands work on both SQL 2k and 2k5 :)