Getting object permissions

I have always wanted a quick way of Scripting the permissions that have been granted
for an object. I felt it would be handy to get the permission details of an table/sp without going to the enterprise manager. So a small script to do the same.

I have wrapped up the script into a SP called 'sp_get_permissions' and uploaded it here.

sp_get_permissions takes object name as parameter and provides the permission script for the specified object. sp_get_permissions also accepts null parameter and provides the permission script for all objects in the database. This would be extremely handy if you are doing deployments between DEV/Staging and Production.

Deploy it in your master database and you can use it from any of your databases.

Sample call:



The sp_get_permission works for both 2000/2005.


Now for the second part of the post..

If you are frequent user of sp_helptext then you would have felt the need
to get the sp/view's create script along with the permissions attached to it.

To do the same I have tweaked the sp_helptext code to make sp_helptext2
which would provide the object's script along with all the permission details.

Download sp_helptext2 from here ,deploy it to your master db and you can use it from any of your databases.

Sample call




Just a small note. These two sps strictly take the object name as the parameter.
So avoid prefixing the object owner name when passing the parameter..ie
EXEC sp_helptext2 'dbo.testProc' doesnt work. But EXEC sp_helptext2 'testProc' does.

@@IDENTITY, SCOPE_INDENTITY(), IDENT_CURRENT() - Comparison

Often when one wants to check the last inserted identity value one of the three commands @@IDENTITY, SCOPE_INDENTITY(), IDENT_CURRENT() is used. In this post we will do a comparsion of the three.

@@Identity:
As most you may be aware @@IDENTITY provides the last inserted identity value
for that particular connection. But the thing to note is, it provides you last inserted identity value on the LAST identity table inserted on that connection. Assume, the inserted table A has a trigger inserting into another table B with a identity column. Then @@identity yields identity value of Table B and not table A.

Ident_current() :

Ident_Current('tablename') provides the last inserted identity value across all connections on the table specified. So it cant be used to find the last inserted identity value by your connection.

Scope_Identity(): Scope_identity() is perhaps the most reliable command to find the last inserted identity value on the table by your connection. Provides the last inserted value by the connection not by the underlying trigger.

Couple of things to note:

>If your connection hasnt inserted any rows then @@Identity/Scope_Identity()
returns null.
>Rollback of transaction doesnt reset the identity

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.