Pythian Group

SQL server folks may find the “non-yielding scheduler” warning familiar and it is usually (if not always) associated with a stack dump.

The “non-yielding scheduler” message comes with warnings like the following:

Process %ld:%ld:%ld (0x%lx) Worker 0x%p appears to be non-yielding on Scheduler %ld. Thread creation time: %time. Approx Thread CPU Used: kernel 0x ms, user 0x ms. Process Utilization 0x%. System Idle 0x%. Interval: 0x ms.

This means that there is a background thread that keeps running and making other worker threads yielding back, in time.

There are many causes of the “non-yielding scheduler” error. Memory dump analysis is necessary to determine the cause. Look up the error in MS Support Advanced Search and you will find plenty of KBs dedicated to it.

You may also find (not all cases) errors 17883, 17884 logged in the event viewer, containing information like this: Read the rest of this entry . . .

This is a little tip for those who develop or debug SSIS packages.

In SQL Server Integration services, User namespace variables are assigned values that are used across the package.

When developing, testing or debugging packages, we assign multiple values to those variables to test different scenarios. This is done using the variables window.

However, SSIS Script tasks can allow us to key in values for selected variables in run time. This looks more fun and keeps us from taking chances when we forget assigning variables’ values.

A tiny example: Read the rest of this entry . . .

It is not unusual that a DBA needs to run the same query across multiple instances. The query can be anything from a simple line to retrieve a specific value (such as an instance version), to others that involve data modifications or schema changes.

SQL Server 2008 brought the ability to execute Statements Against Multiple Servers Simultaneously by simply creating a local server group or broadly using a Central Management Server and one or more server groups. Inside these groups there should be one or more registered servers.

There is also the commercial Red-Gate SQL Multi Script with an Unlimited edition license that allows you to run your code against any number of SQL server instances.

Before SQL Server 2008, there was no native support for running the same query against multiple instances using shipped tools, so it can be approached in one of these ways: Read the rest of this entry . . .

In SQL Server Replication, a regular check is getting the number of undistributed commands for a transactional subscription. This can be done using the replication monitor (SQlmonitor.exe from the RUN prompt) or programmatically by running sp_replmonitorsubscriptionpendingcmds at the Distributor on the distribution database.

One issue with sp_replmonitorsubscriptionpendingcmds is that you will have to submit a few parameters without defaults (the required parameters are mentioned in the above link).

I wanted once to get status information about all subscriptions (Last logged message, status, agent, etc.), plus the number of undistributed commands for each subscription. For the replication info there is the MSdistribution_agents table, which is stored in the Distribution database and contains one row for each Distribution Agent. For the Agents’ history there is the MSdistribution_history table, which contains the history for Distribution agents, and is also stored in the Distribution database.

To return this, my idea was to get a list of publishers, publications, subscribers, and so on, into a temporary table, loop against the result, and run sp_replmonitorsubscriptionpendingcmds for each row, and then join against MSdistribution_history for the final result.

Here it is. All comments and additions are welcome.

Read the rest of this entry . . .

Logon triggers were introduced in SQL server 2005 SP2. They fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.

Logon triggers can be used to audit and control server sessions, in such usage scenarios as:

• Tracking login activity, you can create a user table to audit user sessions.
• Restricting logins to SQL Server, you can prevent a specific login from establishing a user session.
• limiting the number of sessions for a specific login, you can limit a specific login from establishing more than a specific number of user sessions to the instance.

You can extract useful XML data about Logon events inside Logon by using the EVENTDATA function. I will demonstrate a little later how we can use this function to audit log-ins to a user table.

How do Logon triggers work?

The life cycle of a Logon trigger is simple: Read the rest of this entry . . .

I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor.

The server hosts more than 50 databases that serve an ASP.NET application hosted on some servers in a web farm. These servers issue connections to the databases in a distributed manner to balance the web application load. I tried to discover what these connections were doing and to what databases they were connected. Connections grouped by database:

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  db_name(dbid)
order by count(*) desc

This showed some databases having more than 300 connections associated with them.

What about logins used?

select  loginame , nt_username, count(*) 'Connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  loginame , nt_username
order by count(*) desc

This showed a mix of windows domain accounts (those with values in column nt_username, e.g: domain\user) beside SQL authentication accounts (those with column nt_username empty, e.g: “sa”).

In order to reduce the number of times that new connections must be opened, applications may use connection pooling. This was clearly not the case here, and all these connections resulted in what is known as “pool fragmentation”.

So what’s connection pooling? how does it work, what can cause pool fragmentation and how can we avoid/reduce it?

Read the rest of this entry . . .