Sql Server Access Methods Performance Metrics: FreeSpace Scans/sec Table Lock Escalations/sec Workfiles Created/sec

Interesting one today:

Lately, Access Methods performance metrics have been helpful in troubleshooting some recent issues. Access Methods has several important metrics that show the metrics to measure the usage internals of logical data with in Sql Server.

Here we’ll look at 3 of them:

1. \SQLServer:Access Methods\FreeSpace Scans/sec
2. \SQLServer:Access Methods\Table Lock Escalations/sec
3. \SQLServer:Access Methods\Workfiles Created/sec

FreeSpace Scans

Objects in Sql Server are written on database pages. A group of these pages are called Extents (8 pages). Allocation of space occurs in units of extents. Extents are of two types Mixed & Uniform Extents.

Usually small tables (and sometimes Heap tables) are written to Mixed extents. So, when the data in those tables/objects increases, Sql Server needs to find more free space to  accommodate for the growth. In these situation, Sql Server performs Free Space Scans. This counter measure how many times these occur every second, hence FreeSpace Scans/sec.

Not sure what Microsoft’s recommended range on this metric is, but in our environment, this metric stays low i.e. under 5 or 10. So, as a rule of thumb, lets say as long as the number is below 20 we are okay. Anything higher for extended periods of time might need some attention.

So the best approach is to gather baseline first; Then you’ll know what is normal and what is out of the ordinary.

Table Lock Escalations

When a query is trying to read rows from a table, it uses the smallest lock as possible to maintain concurrency. In some rare (but not uncommon) occasions, this lock gets escalated higher level, either Page or Table level. While this reduces concurrency on that table, it improves the efficiency of this particular query execution.

Issuing thousands of locks costs a lot of memory; So it is easier to issue table lock and read as much data as needed. But the down side is that it will prevent other connections from reading this table.

For more, read this previous post on Lock Escalations.

This counter measures, the number of such escalations occur per second. Which this is a common occurrence, higher numbers for extended periods of time are not good. So, look into optimizing queries, so they only query the exact amount of data they need (a.k.a. use better JOINs and WHERE clause conditions)

Workfiles Created

When a large query that handles large data sets is executed, sometimes the intermediary data sets (or virtual tables) are written to disk. This helps with efficient processing of data. Sql Server reads this data into memory as and when needed and completes query processing.

This counter measures, how many work files are created each second. On a busy system, that handles large data set manipulation queries, many WorkFiles & WorkTables are created each second. So, this number needs to be considered in context. Capture a baseline first; Then measure any aberrations from baseline and look into possible reasons.

Usually when a query manipulates large data sets, Sql Server uses Hash Joins to manipulate the data to find matches. So, if you have a lot of queries that perform Hash Joins or Hash Aggregates, this counter spikes up.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : How to enable or disable Sql Agent Jobs using T-SQL

Quick one today:

Occasionally, during deployments, we need disable some Sql Agent jobs as we are making pertinent changes. If it is just one or two, may be doing it manually is okay (not really, but it seems to be more common), but when we have a large list of jobs that need to be disabled and then enabled, it’s always a better idea to rely on T-SQL scripts. Less room for human error.

For this, Sql Server has an internal stored procedure called ‘sp_update_job‘. As it seems obvious, it could do more than just enable/disable jobs. But the example below, only shows one aspect of it.

By change the parameters, we could change many properties of a Sql Agent Job.

--
--  Change Sql Agent Job parameters
--
USE msdb
GO

EXEC dbo.sp_update_job     @job_name = N'JobName'
			,  @enabled = 1
GO

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Perform Differential Backup Command

Quick one today:

Occasionally, the need to perform DIFFERENTIAL backup arises. The underlying mechanism of Differential backup is one of the coolest things.

 

--
--  Syntax for DIFF backup
--
BACKUP DATABASE SampleDB
TO DISK = 'I:\MSSQL\Backup\DIFF\SampleDB.DIFF'
WITH      DIFFERENTIAL
	, COMPRESSION
	, STATS = 1
GO

 

 

Some highlights:

• Differential backup only captures the data that has changed since the last FULL backup (called base of the differential).
  • Even when we perform multiple DIFF backups, it captures all the data that changed since in the last FULL backup (unlike Transactional log backups, where they only take the changes since the last Transactional backup)
• The size of DIFF backup files is usually smaller than FULL backups;
  • Obviously, since we are only capturing the changes since last FULL backup.
• Using Differential Bitmap page, Sql Server maintains a list of extents that were modified since last FULL backup.
  • Using this, Sql is able to quickly run through all the extents that need to be backed-up quickly.
  • See the image from MSDN for clarity
• 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Cluster Set up Error : Network Binding Error. The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configurations to change the binding order

Interesting one today:

Earlier this month, during Sql Server Cluster set up on a new set of VMs, ran into this interesting warning message.

Network binding Order generated a warning.

The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configurations to change the binding order.

Cluster Setup Network Binding Order

Upon further investigation, it became clear that the NIC that connects to the Domain network is not given highest priority (as needed) for Sql Cluster.

Resolution

In Clustered environments, it is recommended to have the network interfaces properly ordered for maximum efficiency.

Go to “Network connections” and open Advanced Settings. See the image below:

Network Connection – Advanced Settings

In the resultant window, under Adapters and Bindings tab, make sure the network interfaces are ordered according to the recommendation. Domain network needs to be on the top, then Heartbeat Network and Remote Access Connections. See the image below, for the recommended order.

Network Binding Proper Order

After saving the new order, go back to “Install Failover Cluster Rules” and re-run the checks. This blog has more info, if interested about the rest of cluster set up.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Free Books

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

Hope this helps,
_Sqltimes

Read Full Post »