About these ads

Archive for the ‘#sqlhelp’ Tag

PREEMPTIVE_OS_GETPROCADDRESS: Are you confusing me?   3 comments


PREEMPTIVE_OS_GETPROCADDRESS is a new wait type that was introduced in SQL Server 2008 to track time taken by GetProcAddress to load the entrypoint in the DLL when Extended Stored Procedure calls are made by the SQL Server instance. However, the way the wait time is tracked by this wait type has a little quirk which Rob Dorr had documented in a blog post.

Based on a discussion on #sqlhelp thread on Twitter, I found the need to demonstrate this unique quirk with the help of an example. I am using the following command to list you the sub-directories on my C: drive using the command:

exec master..xp_dirtree ‘C:\’

Next I monitored the wait statistics for the session executing this Extended Stored procedure using the T-SQL script below with a 1 second delay:

SET NOCOUNT ON
WHILE (1=1)
BEGIN
SELECT [session_id], [status], [command], [start_time], [cpu_time], [reads], [writes], [wait_resource], [wait_time], [last_wait_type], [wait_type]
FROM sys.dm_exec_requests
WHERE [session_id] = 53 -- Session ID that is executing the Extended Stored Procedure

WAITFOR DELAY '00:00:01'
RAISERROR ('', 10, 1, N'Waits');

END

The output of this script was as follows:

session_id status        command          start_time              cpu_time    reads    writes      wait_time   last_wait_type                        wait_type

53         runnable      EXECUTE          2011-07-20 00:59:44.330 62          0        0           712         PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

53         running       EXECUTE          2011-07-20 00:59:44.330 234         0        0           1713        PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

53         runnable      EXECUTE          2011-07-20 00:59:44.330 452         0        0           2714        PREEMPTIVE_OS_GETPROCADDRESS          PREEMPTIVE_OS_GETPROCADDRESS

As you can see above the wait time keeps increasing even though I can see that the session is performing work due to the following results:
a. The results window in SSMS for the session executing the extended stored procedure was churning out results
b. Using a Process Monitor, I could trace the filesystem activity on the C: drive for the SQL Server process

You could run into this issue while using any Extended Stored Procedure (system or user) for a SQL Server 2008 or SQL Server 2008 R2 instance. Since I know that the session is performing work, the only two other things that I would check if there are resource bottlenecks w.r.t. memory or I/O if some filesystem related activity is being performed by the extended stored procedure. Most of the issues where I have see this wait type causing confusion is when the extended stored procedure is performing disk related activity. At that time, Process Monitor will put your doubts to rest along with the information that I mentioned above to confirm that that the procedure is actually doing some work and not stuck or perceived as “hung”.

About these ads

Excel via Linked Servers   Leave a comment


image

Recently, I had replied to post on the #sqlhelp Twitter hashtag regarding configuring a Linked Server to an Excel file using the GUI in Management Studio. This is very much possible. I use a linked server to pull data from .xls file on a 64-bit SQL Server instance for an application that I maintain.

Using the 64-bit ACE provider, you can now do this. The data source which is masked in the above picture is the location of the Excel file with the full file path.

Once you have this configured, you can access the Linked Server catalogs by expanding the Linked Server in Object Explorer. Each table listed in the catalog is actually an Excel sheet.

This is fairly simple task but since this isn’t an explicit example out there for this, I thought I would do a quick post on the same.

Addedum: April 4th, 2010. After my colleague, Evan pointed out the server side support policy for ACE.

Disclaimer: The ACE redistributable link does mention the following:

The Access Database Engine 2010 Redistributable is not intended:

  • As a replacement for the Jet OLEDB Provider in server-side applications.
  • To be used within a service program or web application that relies on a Windows service.
Technorati Tags: ,,

Posted April 1, 2011 by Amit Banerjee in SQL How To, Twost

Tagged with , ,

Removing primary transaction log file   2 comments


Recently I was discussing about removing transaction log files on the #sqlhelp hashtag on Twitter. The question was whether we could remove an existing transaction log file after adding a new one. During the course of the discussion, I learnt that the file that was being removed was the primary transaction log file of the database.

When you attempt to remove the primary log file of a database from Management Studio or using ALTER DATABASE command, you will receive the following error:

Drop failed for LogFile ‘dbLogTest_log’.  (Microsoft.SqlServer.Smo)

The primary data or log file cannot be removed from a database. (Microsoft SQL Server, Error: 5020)

This is by-design. We do not allow the removal of the primary transaction log file of user databases. This was first introduced in SQL Server 2000.

Tibor (Blog), a SQL Server MVP, has already blogged about how to remove transaction log files using T-SQL commands here. So, I am not going to duplicate the post here again by providing the same T-SQL commands.

Posted November 12, 2010 by Amit Banerjee in Did you know, Twost

Tagged with , ,

Follow

Get every new post delivered to your Inbox.

Join 1,290 other followers