Varun Dhawan's Blog

Index usage by %Like% operator – Query Tuning

Ok, so this one is interesting. I was reading an Internal DL (Distribution Group) post on tuning %Like% operator performance. I find some confusion in various places about index selectivity of '%Like%' operator, and I decided to do few test myself. Hopefully test details below can help a bit:

Problem Statement:
- While optimizing high CPU consuming queries on 3rd party application, verified that most of the queries are using '%LIKE%' operator.
- Interestingly enough, while some of these queries are going for "INDEX SEEK" while others are going for "INDEX SCAN". Why ?

Facts:
As per SQL BOL - (http://msdn.microsoft.com/en-us/library/ms179859(SQL.90).aspx)

• 'LIKE' Determines whether a specific character string matches a specified pattern
• During pattern matching, regular characters must exactly match the characters specified in the character string.

More importantly, wildcard char % can be placed in one of below Four ways:

1. SEARCH-STRING%
- The SEARCH-STRING% will perform INDEX SEEK and return data in least possible time.

2. %SEARCH-STRING
- When using %SEARCH-STRING  it's much less likely to use the index, however it still may at least perform an INDEX SCAN on a full or partial range of the index.

3. %SEARCH-STRING% 
- When using %SEARCH-STRING%  it's much less likely to use the index, however it still may at least perform an INDEX SCAN on a full or partial range of the index.

4. SEARCH%STRING
- The SEARCH%STRING will perform INDEX SEEK and generate an initial result set, containing the values that match first string i.e. SEARCH%. Next it will scan through the values to get second string i.e. %STRING

Lab-Test:
Product: SQL Server 2005 (SP2) x64
Database: Northwind
Table: dbo.Customers

Here’s the output from SP_HELPINDEX on dbo.Customers before we proceed. Please note, “Customer” column already has a clustered index on it.

[Example 1.] Select on … SEARCH-STRING%

[Example 2.] Select on …  %SEARCH-STRING

[Example 3.] Select on … %SEARCH-STRING%

[Example 4.] Select on … SEARCH%STRING

Note: SEARCH%STRING will return all values starting with 'S' and ending with 'A'

The moral of the story?  You can tune/speed-up your ‘%Like%’ operator queries by making use of indexes. Just be aware of above limitations and write your queries accordingly.

Enjoy! and Thanks for reading.

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

BCP command fails with ERROR:"User name not provided…", when both SQL Server 2000 and SQL Server 2005 Tools are installed on machine

This blog post has been moved to my team blog site >> http://blogs.msdn.com/sqlserverfaq/archive/2009/09/24/bcp-command-fails-with-error-user-name-not-provided-when-both-sql-server-2000-and-sql-server-2005-tools-are-installed-on-machine.aspx

Enjoy! And, thanks for reading.

Understanding and Troubleshooting ‘Win2003 PF Usage’ on the server running SQL Server 2000

I’ve been recently asked to explain the reason of high PF Usage on windows 2003 server running SQL Server 2000 Enterprise Edition.

Problem Statement:
- From: OS Task Manager > Performance Tab: High value depicted in “PF Usage” graph.
- On stopping SQL Server Service, this gets resolved.

So can we conclude that SQL Server is causing high Page File (PF) usage? you’ll get the answer below.

Lets first determine the configuration:

System Configuration:
- Physical Memory (RAM): 4 GB’s
- Paging file size “c:\pagefile.sys” : 4092 MB’s

SQL Server Configuration:
- SQL Server 2000 Enterprise Edition 32-bit
- AWE : Enabled
- MIN_SERVER_MEMORY: 1 GB’s
- MAX_SERVER_MEMORY: 4 GB’s

Here’s what PF usage graph says…

And the perfmon counter…

- Memory Available Mbytes is very low around 48 Mb’s

- Total memory consumption by SQL is high around 3036 Mb’s

So here’s the answer - finally

What does PF Usage graph reflect?
“Total” PF Usage indicates the current amount of virtual memory that is being used by programs and system processes. Actually, this value is summation of Physical Memory or RAM + Page file in use. So if either of them gets utilized (i.e. Page File or Physical Memory), it will get reflected in the PF usage graph.
This is not reflect the actual page file usage, instead what it shows is the potential page file usage (i.e. if everything in RAM that could be sent to the page-file actually was, this would be the space required)

Then what is actual Page Files usage?
Actual page-file usage, as shown in Perfmon counters is 5% (i.e. 204 MB’s). This is very nominal.

So here, does this indicate high utilization of Physical Memory by SQL Server?
Yes!

Why is SQL consuming high memory, when there is no work load?
- Here, the MAX_SERVER_MEMORY = 4 GB’s (Total RAM) and AWE = enabled
- So all physical memory is grabbed by SQL when SQL services is started.

Here’s what SQL BOL Server Memory Options has to says: “Instances of SQL Server 2000 running in Address Windowing Extensions (AWE) memory mode do allocate all the FULL amount of memory specified in MAX_SERVER_MEMORY on server start-up.”

So, it is always recommended to limit SQL Server Physical Memory usage by setting MAX_SERVER_MEMORY.

Hope that answers!

Technical References:
Managing AWE Memory: http://msdn.microsoft.com/en-us/library/aa213764(SQL.80).aspx
Server Memory Options: http://msdn.microsoft.com/en-us/library/aa196734(SQL.80).aspx

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Steps to change the location of SQL Server / SQL Agent Errorlog files

Recently came across a customer scenario, wherein the 'ask' was to change the current location of SQL Server Errorlog files to a different location. Here’s a simple way to get this done:

Change Location of SQL Server Errorlog files

1. First, identify the current location of SQL Server Errorlog files.

Refer my previous post on How to: Verify path for SQL Server Error Log Files

2. For error log parameter "-e", change the current path to new location.

-e<New_Errorlog_location_here>\ERRORLOG

WARNING: Make sure that correct folder exists else consecutive attempt to start SQL Server service will fail with error “The request failed or the service did not respond in a timely fashion….”

3. Re-cycle the SQL Server service for changes to take effect.

Additionally, to change location of SQL Server Agent Errorlog files

1. This is relatively simple and can be done using below code snippet:

USE msdb
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'<New_Errorlog_Location>\SQLAGENT.OUT'
GO

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

How to: Archive more that six SQL Server Error Logs using SSMS / EM

The following post describes how you can configure your SQL Server to archive more than six Errorlog files using SSMS (2008, 2005) or Enterprise Manager GUI.

While I was aware of MS KB # 196909, that describes a way of increasing the number of SQL Server error logs, by way of Registry change, I was looking for something simpler via GUI.

I could finally found a way of doing this from MS-SQL GUI (SSMS or EM) as below:

For SQL 2008 and SQL 2005: via SQL Management Studio

Screen 1.

Screen 2.

For SQL 2000: via Enterprise Manager

Screen 1.

Screen 2.

Well, that actually made my job lil simpler !

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Error “Failed to open a file. Access is denied”, when attempting to open SQL 2005 Profiler Trace

Hello All,

Yesterday while doing the regular stuff, I was reported a strange error related to profiler traces. I broke my head for a day almost to figure out what was happening.

Issue: I have a SQL Agent Job that creates profiler trace files using sp_trace_create. The job is being owned by my ‘Domain’ A/c which is also SQL Service start-up account.

My colleague (say user-X), when attempts to open the trace files gets error >>

“Failed to open a file. Access is denied”.

This is strange, since there another SQL 2000 Instance, where he can successfully open the trace files. He has NO access on both SQL 2005 and SQL 2000 instances. After several hours of agonizing and troubleshooting, I finally got this on CSS Blog >> How It Works: Trace (.TRC) File Security

“SQL Server 2005 has a different trace file security as compared to previous build of SQL 2000. Per new security design.The owner of the trace files must explicitly grant security permissions to others in accordance with security policies and company guidelines”

So a short solution to this is to grant user-X access to SQL Server.

However, what if I don’t want to have user-X any access on SQL Server. Here’s the workaround I tried and it worked perfect for me:

• Use NTFS Permissions Inheritance in Windows

- Create a shared-folder where both Job-owner and user-X have full access

- Job-owner copies the trace files and paste them to above shared-folder

- Now, user-X will be able to open the trace files without any issue, since the parent folder's permissions are inherited by all files of the parent.

Refer to Microsoft KB Control NTFS Permissions Inheritance in Windows for more details.

If you happen to think a better workaround or a different idea - I'm all ears.

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Backup SQL Server Database to a network shared drive

Hello All,

You have a situation wherein you want to backup SQL Server database to a network mapped drive. So all you thought of doing is mapping the drive from Operating System with a Drive Letter. Now you tried taking the backup from SSMS backup GUI, however you cannot view the network mapped drive.

As per SQL Server Books-On-Line:

“For a network share to be visible to SQL Server, the share must be mapped as a network drive in the session in which SQL Server is running”

So to backup your database to a network mapped drive, you need to follow below steps:

Prerequisite: The steps are applicable on machines running under “Domain Account”.

Step # 1. Map the network drive:
EXEC xp_cmdshell 'net use <drivename> <share name>'

Ex: EXEC xp_cmdshell 'net use H: \\machinename\sharename'

Step # 2. Verify drive mapping:
EXEC xp_cmdshell 'Dir H:'

Step # 3. Delete the network map drive
EXEC xp_cmdshell 'net use H: /delete'

Once done, you will be able to view the network mapped drive from Backup GUI.

How to make 'network drive mapping' permanent across SQL Server reboot:

Option 1. – Using Backup Device

- After completing Step # 1. and Step # 2. Create a “Backup Device” for above network mapped drive. For details refer >> How to: Define a Logical Backup Device for a Disk File

- Once “Backup Device” is created, network mapped drive will be visible across SQL Server reboot.

Option 2. – Using “start-up” Stored Procedure

Step 1. Create a Procedure

CREATE PROC map_drive_satrtup
As 
EXEC xp_cmdshell 'net use <drivename> <share name>' 

Step 2.  Set Procedure Options

sp_procoption  @ProcName = 'map_drive_satrtup' 
, @OptionName = 'startup' 
, @OptionValue = 'on' 

Getting ERROR????

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.

Indicates that you need to enabled xp_cmdshell. Use below command:

sp_configure 'xp_cmdshell',1; 
Go 
RECONFIGURE WITH OVERRIDE; 
Go

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

SSMS Error “HRESULT: 0x80131040” encountered while opening a Table in Design-View

Hello,

April 09, has lot more than expected. During my usual stuff, I was trying to open a table in “Design” view, I got this error

Analysis:

Started my search on the ERROR, and I found a Blog-Link. This explains that “Error is encountered when we try to open a table with SQL Server Management Studio 2005 after installing SQL Server 2008 (Katmai - CTP November 2007) for testing purposes on the same machine.”

However, it’s been ages that SQL2K5 and SQL2K8 been working fine on my machine. I then verified the solutions steps. It suggests to rebuild SQL Server 2005 Registry by running setup from command prompt. As this needs downtime, I was eager to explore alternate options.

Solution Steps:

There are few things specific to my box:

• I have SQL 2005 and SQL 2008 both installed on same server

MSDN Link ASP.NET IIS Registration Tool explains “When multiple versions of the .NET Framework are executing side-by-side on a single computer, the ASP.NET ISAPI version mapped to an ASP.NET application determines which version of the common language runtime (CLR) is used for the application. The ASP.NET IIS Registration Tool (Aspnet_regiis.exe) allows an administrator or installation program to easily update the script maps for an ASP.NET application to point to the ASP.NET ISAPI version that is associated with the tool”

Ran Below command:

aspnet_regiis –i

• The command installs the ASP.NET version that is associated with the ASP.NET IIS Registration tool and updates the script maps of all existing ASP.NET applications.
• Note that only applications that are currently mapped to an earlier version of ASP.NET are affected.

The Table “Design” view is fine again and I’m sorted :)

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Unable to Drop Data-File – Error: Time-out occurred while waiting for buffer latch type 3

Hello All,

This month have been filled with interesting and thought provoking issues. Here's a new one

In a recent scenario, I tried dropping a “secondary” data-file in SQL 2005. Got below error

Please Note, I have already did, Empty file by migrating the data to other files in the same file-group.

As Per KB # 898709 latch type 3 indicates that the SQL Server process is trying to acquire an update (UP) latch.

This is what I did to resolve the issue:

1. Set the database to SINGLE_USER
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

2. Remove the Secondary data-file
USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

3. Set the database back to MULTI_USER
USE master;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

It worked !

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

How To: Improve SQL Server Management Studio Start-up Time

Hi Friends,

Here's something interesting about Management Studio 2005 (SSMS)

• When you launch SQL Server Management Studio 2005 on the server, it takes a couple of minutes (more than 5 mins in some case) before it  starts up and another few minutes to connect.
• And, you observe from Task Manager that, there are sufficient (Memory and CPU) resources available.

Here’s few Tips to improve the SSMS ‘Start-up’ time:

Step #1. Un-Check Certificate Revocation

- Go to "Start" -> "Programs" -> "Internet Explorer"

- Go to "Internet Options" -> "Advanced" -> "Security" -> [ ] Check for publisher's certificate revocation

- Go to "Internet Options" -> "Advanced" -> "Security" -> [ ] Check for server certificate revocation

Step #2. Disable SQL Server Error and Usage Reporting

- Go to "Start" -> "Programs" -> "Microsoft SQL Server 2005".

- Open "SQL Server Error and Usage Reporting"

- Uncheck the following checkboxes:

- "Send error reports for all components and instances of SQL 2005 to Microsoft or your corporate error reporting server."

Note: Using local "corporate error reporting server", may require to enable this feather.

- "Send feather usage reports for all components and instances of SQL 2005 to Microsoft."

Step # 3. Disable Online Help Content for SQL Management Studio

- Go to "SQL Management Studio" –> "Tools" –> "Options" –> "Environment" –> "Help" –> "Online" –> "When loading help content"

- Select "Try local first, then online" or "Try local only"

#4. Enable Shared Memory and TCP/IP in SQL Server Configuration Manager

- Go to "Start" -> "Programs" -> "Microsoft SQL Server 2005" -> "SQL Server Configuration Manager"

- Go to "SQL Native Client Configuration" -> "Client Protocols"

- Right-click Enable "Shared Memory"

- Right-click Enable "TCP/IP"

Step #5. Configure Internet Connection for proxy

- Configure a proxy server to allow access to http://crl.microsoft.com from your server

Also, do refer to Microsoft Knowledge Base # 555686 for more details.

Hope this will help !

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

How to: Copy “Column Header” in SQL Management Studio 2008

Hello All,

SQL Server Management Studio 2008 (SSMS) has never failed to surprise me with it’s little (yet useful) enhancements. Here’s adding to the list:

As a common need, you are required to Save/Copy SSMS query output to other files (E.g. Excel). This however had a limitation that you could NOT copy the ‘Column Headers’.

In SQL Server Management Studio 2008, you can actually Copy “Column Headers” from query results window. Here’s how:

1. In SSMS, Result window. Right-Click on column name

2. Select Copy with Header option (refer below screen)

What’s more, you can manage SQL 2005 database from SQL 2008 Management Studio and get benefits of SSMS enhancements, like one above.

Try, You Got to Enjoy It !

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

How to: Verify path for SQL Server Error Log Files

Hello Friends,

March has been a busy month, and it is not over yet. Taking some time off to blog; small finding “worth sharing”.

When managing SQL Server there are so many different locations to look for MSSQL files, like SQL Server Error Logs. While we can easily locate path to SQL Server Error Logs using SSMS (management studio), here’s is an alternate method (Just-In-Case the Management Studio errors while launching)

SQL SERVER 2005

Using SQL Server Configuration Manager:

SQL Server Configuration Manager is a tool to manage the services associated with SQL Server. The features however are more:

1.       On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2.       In SQL Server Configuration Manager, expand Services, and then click SQL Server

3.       In the details pane, right-click the name of the instance you want to start automatically, and then click Properties

4.       Click the Advanced tab, and Verify Startup Parameters.

·         -e The fully qualified path for the error log file  (Refer below screen)

Other parameters

·         -d The fully qualified path master database file

·         -l  The fully qualified path master database log  file

As always, for more details on SQL Server Configuration Manager, Refer BOL >> http://technet.microsoft.com/en-us/library/ms174212(SQL.90).aspx

SQL SERVER 2000

Now as we don’t have “SQL Server Configuration Manager” for SQL Server 2000, I need to look for an alternate approach to locate SQL Server Error logs (assuming that Enterprise Manager is not working). Here’s how I got there…

For - Default Instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters

For - Named Instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft SQL Server\<Instance_Name>\MSSQLServer\Parameters

Go Try This !

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Lock Pages in Memory in SQL Server 64-Bit

Hello All,

Well after a long time of no writing, I finally found an interesting topic and also some time to write.

Lately, I’ve encountered couple of instances of Troubleshooting Memory Pressure on SQL Server 2005 64-Bit version. Doing some basic configuration check, one realizes that ‘Lock Pages in Memory’ Operating System Privilege is not granted.

On further probing, came a common question:

Question:         “Weather we require Lock Pages in Memory in SQL Server 64-Bit”?

Answer:            YES

Here’s why:

In a typical scenario of SQL Server witnessing memory pressure, OS can page out SQL Server Pages. Enabling Lock Pages in memory will prevent operating system from paging out the working set of the SQL Server process.

Per SQL Books online (BOL), “This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk”

BOL further says, “Locking pages in memory is not required on 64-bit operating systems”. This however has some exceptions. In SQL Server 2005 64-bit, there are various memory related issues can be encountered causing SQL Server ‘Performance Degradation’. For more details, refer >> Knowledge Base http://support.microsoft.com/kb/918483

The suggested workaround here is to implement ‘Lock Pages in Memory’

Next steps:

Benefits of Backup Compression - Faster BACKUP / RECOVERY

This is in continuation to my blog that I posted on 23rd Dec, 2008 on “SQL Server 2008 Backup Compression”. While discussing about Benefits of SQL Server Backup Compression, I talked about “Faster Database Recoveries from Compressed Backups”. This might have raised questions “How do we achieve faster recoveries from compressed backup sets, when we are spending additional CPU time for decompressing the backup”.  I too had similar thoughts initially.

Here’s how I got the answer…

In a normal SQL Server DB backup – Wait time is on CPU and I/O. (Remember, Backups are an I/O intensive operations.)

In case of a SQL 2008 Compressed backup – We are still spending time on CPU and I/O. However,  since we are now dealing with compressed backups (small size backup files), hence we are spending less time in I/O, but more time in CPU as we are de-compressing backups at the time of recovery. This answers our questions.

Now, let’s check this with an example. In below example, let’s monitor the SQL Server WAITTYPES while performing a database backup/recovery from compressed and uncompressed database backups.

Test Scenario: To Determine Backup I/O

CASE A. UN-COMPRESSED Backup

- I have a database named ‘A_large_database’ of size   10174.25 MB’s (or 10 GB’s approx)

- And, I took a normal database backup (as below)

-----------------------------------------------

How to identify weather a Database Backup Set was compressed using WITH COMPRESSION

This is in continuation with my first blog “SQL Server 2008 Backup Compression”. I received this query from one of the reader about “How to determine, if a Database Backup set was compressed using WITH COMPRESSION”. A simple way to identify this to use RESTORE HEADERONLY.

RESTORE HEADERONLY command can be used to fetch all information about a backup residing on a backup device. The command output includes a column called ‘Compressed’ with values as 0 or 1. Whether the backup set is compressed using software-based compression:

• 0 = No
• 1 = Yes

Let’s verify this with an example:

Please note, we are reusing our existing backup sets ‘Before_Compression.bak’ and ‘With_Compression.bak’.

-----------------------------------------------------

RESTORE HEADERONLY

FROM DISK = 'D:\tempdb\Before_Compression.bak'

-----------------------------------------------------

and now,

-----------------------------------------------------

RESTORE HEADERONLY

FROM DISK = 'D:\tempdb\With_Compression.bak'

-----------------------------------------------------

As always, for more details of RESTORE HEADERONLY, I request you to please refer SQL Server 2008 BOL >>

http://technet.microsoft.com/en-us/library/ms178536.aspx

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.