Why “Extent Scan fragmentation” remains high after rebuilding index

According to BOL, extent scan fragmentation is; “Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.”

It is also referred to as “physical fragmentation”, which basically means that the extents on the disk are not contagious.
If rebuilding an index did not bring this value down, it means your physical volume is fragmented.

In my case, I have a sql server running on a netapp san (FAS 3140)

I have a table called objects containing 12+ million records and a clustered index and a couple of non clustered indexes.

When running the following command, I get extent scan fragmentation of 60.59%
dbcc showcontig (Objects,IX_ObjectsExpires)

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 30593
- Extents Scanned..............................: 3852
- Extent Switches..............................: 3917
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 97.63% [3825:3918]
- Logical Scan Fragmentation ..................: 0.88%
- Extent Scan Fragmentation ...................: 60.59%
- Avg. Bytes Free per Page.....................: 683.2
- Avg. Page Density (full).....................: 91.56%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Alter rebuilding the index

Alter index IX_ObjectsExpires ON [Objects] Rebuild;
The value of extent scan fragmentation is gone up!

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 31109
- Extents Scanned..............................: 3889
- Extent Switches..............................: 3888
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [3889:3889]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 69.17%
- Avg. Bytes Free per Page.....................: 806.1
- Avg. Page Density (full).....................: 90.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- as can be seen, the logical fragmentation has dropped to 0, but the extent fragmentation has gone up!!

I run “reallocate” command on the volume containing the database. please note that this is a Netap specific command that reorders the block on a volume. Your SAN vendor will provide a different command.

reallocate start -f /vol/ascio_otherdb_data_vol

During reallocate, cpu gone up from 1 to around 27 % on the filer.
But no performance hit was noticed on the database. After the reallocation was completed, I rebuild the index again;

alter index IX_ObjectsExpires ON [Objects] Rebuild;

and hooray ..extent fragmentation set back to almost 0.15%

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 31109
- Extents Scanned..............................: 3889
- Extent Switches..............................: 3888
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [3889:3889]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.15%
- Avg. Bytes Free per Page.....................: 806.0
- Avg. Page Density (full).....................: 90.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


-- for a volume size of 400 GB, it took around 1 hour t complete the reallocation command on the filer.

Mon Mar 7 17:28:52 GMT [LDN-HA-SAN01: wafl.scan.start:info]: Starting file reallocating on volume ascio_otherdb_data_vol.
Mon Mar 7 18:26:41 GMT [LDN-HA-SAN01: wafl.reallocate.full.done:info]: Full reallocation on '/vol/ascio_otherdb_data_vol' is complete.

CXPACKET wait times and Degree of Parallelism

I have heard many times that the quickest way to improve the performance of a Sql Server instance is to throw more hardware at it. You would think this makes sense, but I have seen doubling the number of CPUs from (8 to 16) decreased the servers performance !

The reason is, under the default server settings, Sql Server will try to execute queries across all the CPUs in parallel. Depending on the type of the workload (OLTP or OLAP), the queries may take much longer to execute as splitting the query plan and combining the result again could take a lot loner than executing the query serially or with less number of CPUs.

Generally you will notice the parallelism issue with servers having more than 8 CPUs.
A quick way to check if your server is suffering from excessive parallelism is to look at the CXPACKET wait times.

This query will give you the top 10 wait types.

select top 10 * from sys.dm_os_wait_stats order by wait_time_ms desc

If you find CXPACKET value at the top of the list and you have more than 8 CPUs, you will probably benefit from lowering the degree of parallelism.
Also note that completely disabling parallelism (setting value to 1) may adversely affect large queries (OLAP). So you need to find the best value for your environment.
You can set the MAXDOP for individual queries which will override the server settings.

In order to change the degree of parallelism from the default (0) which means use all available CPUs to (say 4)

-- set to number of physical processors
EXEC sys.sp_configure N'max degree of parallelism', 4
GO
RECONFIGURE WITH OVERRIDE
GO

This will take effect immediately and no service restart is required.

Another option that is related to the degree of parallelism is the threshold (in seconds) that sql server will check before deciding to use parallelism.
The default value is 5 (i.e. Sql server will only use parallelism if the cost of executing a query serially is more than 5 seconds)

The following statement will modify this setting so that, sql server will only use parallelism for queries that take longer than 10 seconds to execute.

-- number of seconds elapsed
EXEC sys.sp_configure N'cost threshold for parallelism', N'10'
GO

The pictures below shows the affect of lowering MAXDOP on one of my servers on the processor queue length.

There is no rule of what value you should use, it all depends on your specific environment. But here are a few guidelines. Create a baseline of your server so that you can compare the performance of your server before and after the change.

upgrading sql server 2008 to SP2 breaks reports

After upgrading an instacne of sql server 2008 to SP2, I noticed that non of the reports from the management studio are accessable. including the reports that I frequently look at under the Management Data Warehouse.

The error message I was getting was;

The file 'Microsoft.ReportViewer.WinForms, version=9.0.0.0, Calture=neutral, publicKeyToken=b03xxxxxxx' cannot
be opened. Do you want to remove the reference to it from the Recent list?

In order to solve this, you need to download and install the Reporting Services Report Viewer 9.0 available from the following link;

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6ae0aa19-3e6c-474c-9d57-05b2347456b1


installation was very quick and did not require any restart.

Display different colour for different query windows in SSMS

When working with multiple query windows in SQL server management studio, It is useful to visually distinguish which server/ environment you are connected to.
Here is how you can configure SSMS to show a different colour for each environment.

In the Views menu click on Registered Servers "CTL-ALT-G"
Right click on the Local Server Groups under Database Engine and create some server groups e.g. (DEV, SIT , Demo, Prod)

Then right click on one of the new groups and create “New Server Registration”
In the “General” tab of New server Registration, configure the connection details
In the “Connection properties” tab, there is an option to “use custom color”. Tick the check box and pick your favourite colour
Once that is setup, every time you start a new query (right click on the server and select new Query) the query window will show the configured colour in the task bar.

Stored procedure slower than ad-hoc query

This morning I got a call from a developer saying that he is getting timeout on a call to a procedure. can you investigate please? We have orders that are piling up and not getting processed.! top priority

The developer sent me a windows form that i could use to test the issue. He was claiming that when calling the procedure, it takes 30 seconds then he gets a timeout but when using inline sql the query runs instantly.

Grabbed a coffee, fired the win form using the stored procedure and yes I got a timeout after around 30 seconds, then run the same thing using the inline query and it took a couple of milliseconds..himm

Executed the query that the procedure uses from management studio and it took a couple of seconds! I looked at the query plan, it was using an index seek and no issues there, no blocking too..

Sql server was using a different plan (in this case, a bad plan) for the procedure and a different plan when executing the ad-hoc query.

The issue happened to be that SQL Server creates an execution plan for a stored procedure when it is first called, and it tries to be cleaver by optimising it for the parameter that was used i the first call. However, this presents a problem if there is a large difference in the distribution of the number of records based on different parameters.


Let me explain:
If I execuete a procedure the first time and pass a parameter that returns 1 record from a table of 100 million record. Sql server creates a plan optimised for this parameter. If I then call the same procedure and pass a different parameter than returns 5000 records, assuming the first plan was a using an index seek and a key lookup, it will do the same for 5000 records which will be very inefficient.

There are a couple of ways round this;
1. to create local variables inside a stored procedure and assign the procedure variables to teh local variables. this way sql server will not be able to sniff the local variables.

2. use the OPTIMSE FOR () Hint


Create Procedure [dbo].[sp_GetObjectsByAttribute]
(
@AttrId int,
@Value varchar(128),
@Owner varchar(128)
)
As
SET nocount ON

SELECT TOP 500 o.ID, o.Handle, o.ClassID, v.AttributeID, o.Flags,o.Owner
FROM dbo.ObjectValues v
INNER JOIN dbo.[Objects] o on o.ID = v.ObjectID
WHERE
v.AttributeID = @AttrID
AND v.Value = @Value
AND o.Owner= @Owner
AND o.Flags & 1 = 0
OPTION (OPTIMIZE FOR UNKNOWN)
3. Force recompilation of the procedure evey time it is executed (not recommended)


This is a good post
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Unable to login to Navision after restoring database onto a new server

After restoring a Navision database from live server to the development server. it is not possible to login to the database using sa.

if you run the follwing sql statement, you will get
use [databaseName]
go
sp_change_users_login @action='Report'
go


UserName UserSID
dbo 0x90CEEB2B9F356D48B3EDFBFE736524C4

This shows that the username dbo is not associated with any logins. i.e. orphaned user. this happens because the logins are stored in the master database which is different from the master database on the development server.

changing the db owner of the database does not resolve this issue.
EXEC sp_changedbowner 'sa'

but fixed the orphaned user issue if the orphaned user is 'dbo'
so, after running EXEC sp_changedbowner 'sa', the database owner will be changed to 'sa' but you will still not be able to login to Navision with sa.

If you try to login using the Navision interface, you will get the folliwing message;

The user ID and password are invalid. Try again.

the only way i have found to solve this is to delete the users from the Navision [user} table. This is ok, if you are setting up navision on the develoment enviroment, but please be careful in deleting anything on produciton!!

so, in my case running the follwing command fixed the issue;
delete from [mydatabase].dbo.[User]

Why I prefer Log shipping over Mirroring

• I have more than one standby server (a local standby server within the same data centre and one in another country). Mirroring only allows for one standby server.
• I can re-use the standby server for reporting by setting the databases to restore with STANDBY. (You have to delay restoring the logs while the standby is accessed for reports otherwise the restore will fail). One way to do this is to allow reports to run during working hours say 09:00 – 17:00 and schedule the restore jobs to run after out of hours (17:00 – 09:00).
• In my case (15-30 minutes) downtime is acceptable failover time. If you are looking for less downtime, then you need to look at a combination Mirroring, Clustering and Log shipping.
• I don’t have reliable bandwidth to our DR site. This means when the link is down, the log files in the principle server will grow until the link is restored which is an issue as my databases generate huge transaction logs. In my case, all my transaction log backups are compressed as I use a 3rd party tool. I can even move them to another server if they get too many until the link to the DR site is restored.
• I have around 30 databases. If you have more than 10 databases, you should consider what impact Mirroring will have on performance as each database requires two threads. This is not the case with log shipping.
• I can decide when to apply the logs onto the standby server for instance I apply the logs after 20 minutes to catch any accidental/ malicious change to propagate to the standby). In the mirroring scenario, the change is instantly reflected in the mirrored database.
• I like keeping my databases in Bulk-Recovery mode to reduce the amount of data that goes into the transaction log for the minimally-logged operations. If you are looking at mirroring, you databases must be in Full recovery mode.
• I am limited to Synchronous mirroring as I don’t have the Enterprise Edition of Sql server. This is an issue for me as performance matters in my case (mainly due to the latency between the two sites).
• Also, to populate my data warehouse I extract from the standby server (so my ETL has virtually no impact on my production databases!). During the ETL process I don’t restore logs for 1 hour.
• Our application is legacy asp code that cannot automatically re-connect to the mirror in the case of failure (.net code can automatically re-connect in the case of mirroring failover). We still have to do manual changes for the failover to happen.