I have had multiple questions on how users wanted to remove the quotation marks while importing the data into a SQL Server database table while using BCP or BULK INSERT. Last month I worked on a similar issue where the need was to strip out the quotation marks for all the data columns present in the .DAT file. Read my post on SQLServerFAQ to find out how this can be accomplished using BCP, BULK INSERT or if you like using UI, the Export/Import Wizard.
Category Archives: Tools
How to identify the SQL Server version from a Profiler Trace
You use certain little tips and tricks with the SQL Server tools which are not documented anywhere. This is one of them. I needed to find out the version of a SQL Server instance from which the Profiler Trace was captured. I opened the Profiler trace using SQL Profiler 2008. I clicked on the Trace properties and lo behold I had the SQL Server instance name and version.
I got the SQL Server release, version and the instance name (Trace provider name).
Initial Data Collection Script
Where would you be able to use it?
The cases where you can use this script effectively are:
1. Performance issues where a quick initial look at the general state of the server is very valuable.
2. Situations where issue is actually happening right now. However, that said, it is always good to collect post mortem data as well for further diagnostics. Performance Dashboard is a good option here but if you want a snapshot of the server and store it somewhere, then this script can help you.
3. Situations where the problem is happening and you need to quickly verify that you are not hitting some known issues.
How is this different from the other scripts that are already existing?
There are scripts already out there which do the same thing. The advantage here is the fact that the output is in HTML format with bookmarks which makes it easy to determine what information is available. Also, it is easy to get to the sections that are of interest in the current scenario.
Usage
- Download the script using the link given at the bottom of the page and save it to a file named INITIAL_DIAG.SQL. Open the script file in a SSMS Query Window.
- Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
- Execute the script and specify INITIAL_DIAG.html as the output file name so that we can get the output in the require HTML format.
Sample Output
********************************************************************************************
INITIAL MICROSOFT PSS DATA COLLECTION
********************************************************************************************
INDEX
1. General Server Configuration
a. Server Info
b. Non-default sp_configure settings
c. Server Settings
d. Active Trace Flags
e. Profiler Trace Information
2. Memory Diagnostics
a.DBCC MEMORYSTATUS output
b.Memory Clerks rollup output
3.Procedure Cache Statistics
4.Database Diagnostics
a.Replicated Databases
b.Database File Usage Stats
c.Index Statistics
d.sp_helpdb output
e.DBCC SQLPERF (LOGSPACE)
f.Hypothetical Indexes
5.Missing Indexes report
6.Blocking Information
7.Batch/Query Statistics
########################################
****************** SERVER INFO *********
########################################
—————————————————————–
Microsoft SQL Server 2005 – 9.00.3257.00 (X64)
Jun 12 2008 16:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)
—————————————————————
SQL SERVER NAME: SERVER1
CLUSTERED: No
MACHINE NAME: SERVER1
NET BIOS NAME: SERVER1
SQL PID: 5076
FTS Installed: YES
Security Mode: MIXED
Current SPID: 51
Click here to download the script.
Modification: June 20, 2012: Added additional code to account for the following:
- Added code to identify read-write workload ratios
- Added code to find out non-Microsoft DLLs loaded in SQL address space using sys.dm_os_loaded_modules
- Added code to get output for SQL Server 2012 instances
How to troubleshoot Windows Internal Database setup issues
Windows Internal Database is bundled along with Windows applications that need to use a backend database solely for their application purpose. Eg: WSUS, Sharepoint.
The application’s installation files will call the MSI package for installing the Windows Installer Database. In case the application setup fails due to a failure in installing the Windows Internal Database, then you would need the MSI log file for the WID MSI package. This can be found normally in the <System Drive:>\Windows folder and would have the following naming convention: *WSSEE*.log. If you are unable to locate the file, then find out all *.log files in the folder which were created recently.
Once you have located the file, you would need to search for the following string “Return Value 3” in the file.
Eg:
MSI (s) (E0:88) [16:02:14:252]: Product: Windows Internal Database — Error 1402. Could not open key: <Reg Key Name>. System error 5. Verify that you have sufficient access to that key, or contact your support personnel.
Error 1402. Could not open key: <Reg Key Name> System error 5. Verify that you have sufficient access to that key, or contact your support personnel.
Action ended 16:02:14: InstallFinalize. Return value 3.
If you check what Operating System error 5 stands for, you will find out that it is a permissions issue.
C:\>net helpmsg 5
Access is denied.
Based on the error message, you will have to take corrective actions and re-run the setup.
Why can I not produce the same plan with a copy of the production database?
This question has been asked multiple times for SQL Server 2005/2008 environments. Well, the answer is SQL Server has become a bit smarter when it comes to the Query Optimizer. Now it takes into account the hardware environment and database session state also. (Sometimes, the optimizer may be a bit too smart for it’s own good 🙂 but that is discussion not being addressed in this thread)
To determine an optimal query plan, SQL Server 2005 and 2008 uses the following information:
- The database metadata – The table statistics should hold the same information i.e. same of data distribution.
- The hardware environment – Is the Physical Memory (RAM) and the number of Processors identical to the letter on the two machines.
- The database session state
Typically, you must simulate all these same types of information if you want to reproduce the behavior of the query optimizer on a test system. If you are lucky, then without 2 & 3 being satisfied, you might land up with the same plan. In scenarios where you don’t, Option 2 & 3 would be a good option to simulate before running off to solve the question:
Why is Server A generating a better plan than Server B?
With the advent of Virtualization, the simulation of the physical memory and CPU processors is not that big a deal as before. Just thought I would answer this question because many time I have been asked to explain why the same database backup doesn’t produce the same plan as Server A. The first option is to always create statistics-only copy of your database and see if you can reproduce the plan that you see on your production server. If yes, then you can safely proceed to the next step of troubleshooting the bad plan i.e. find out the most expensive part of the plan and take necessary steps to tune it like adding covering indexes, defining statistics, re-placing the join order, adding query hints etc.
Very often SQL CSS team would require a statistics clone of your database, so that they can reproduce the issue in-house. This would not contain any data from the tables but a clone of the database metadata. So, in case you need to troubleshoot a performance issue where you suspect the query plan to be the culprit, you also use a statistics clone and use that on a test server to check if you reproduce the so-called “bad” plan. The reason I mention a test environment because sometimes it is not possible to troubleshoot a query performance issue on a production server. And generating a database statistics clone for a few tables is much faster than a backup restore of the entire database.
You can use the information mentioned in the article below to create a statistics clone of your database:
How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005 and in SQL Server 2008
http://support.microsoft.com/?kbid=914288
TroubleshootingSQL 