DBCC SHOWCONTIG - Key values indicating fragmentation.

This blog shows you which key values to look for in the results of DBCC SHOWCONTIG.

DBCC SHOWCONTIG :: Viewing fragmentation data.

Use DBCC SHOWCONTIG to view how much fragmentation an index has.

Use the TABLERESULTS options to get extra columns in the output that describe statistics about the index.


When looking at the results of DBCC SHOWCONTIG you should concentrate initially on the following metrics:

Scan Density: This percentage should be as close to 100% as possible.

Shows the ratio between the Best Count of extents that should be necessary to read when scanning all the pages of the index, and the Actual Count of extents that was read. In an ideal environment, the density displayed would be close to 100% Defining an acceptable level is difficult, but anything under 75% definitely indicates external fragmentation.


Logical Scan Fragmentation:  Should be as close to 0% as possible

Shows the ratio of pages that are out of logical order. The value should be as close to 0% as possible and anything over 10% indicates external fragmentation. Logical Scan Fragmentation should be ignored for tables that do not have a clustered index

Avg. Page Density (full):

Shows how full the pages are on average. A high percentage means the pages are almost full, and a low percentage indicates a lot of free space. Avg. Page Density should be analysed alongside the fill factor setting specified to help you decide whether or not the index is internally fragmented.


n.b.: The value for Extent Scan Fragmentation is not really important. This is because the algorithm that calculates it does not work for indexes that span over multiple files.

This blog should give you a quick start. The following links will provide you with much more detail and understanding of the metrics, fragmentation and indexes:

Useful Links:

http://www.sqlservercentral.com/articles/Performance+Tuning/whocaresaboutfillfactor/1196/
http://www.sqlteam.com/article/sql-server-indexes-the-basics
http://www.sql-server-performance.com/2002/dt-dbcc-showcontig/

How to Display Line Numbers in the Query Editor - SSMS 2005 and 2008

SQL Server 2008:

In SSMS go to Tools > Options to bring the window up below. Select 'Text Editor' then 'Transact-SQL'. Select the 'Line Number' option from the 'Display' section and click OK.

:


SQL Server 2005:

In SSMS go to Tools > Options to bring the window up below. Select 'Text Editor' then 'Plain Text'. Select the 'Line Number' option from the 'Display' section and click OK.

SQLCMD -L :: List all server instances using SQLCMD Utility and output file to .txt

Utilise the following script in the command line using the SQLCMD utility to output a list of all server instances to a text file:

SQLCMD -L > c:\serverlistall.txt

n.b. This example outputs the results on to my c: drive. Alter the file name 'serverlistall' and directory accordingly.

What Does x64 and x86 Mean?

• x86 means 32 bit
• x64 means 64 bit

e.g. Windows XP is a 32 bit (x86) Operating System, Windows Vista is a 64 bit Operating System.

Find all Database ID's on an instance using TSQL - SSMS (SELECT * FROM sysdatabases)

To return a list of all databases and their Database ID numbers use the following SQL against a specific instance.

USE
GO
SELECT master * FROM sysdatabases

Useful Links: http://msdn.microsoft.com/en-us/library/ms186274.aspx

How to find what SQL Server version you have via TSQL

There are two methods you can use to find the SQL Server version via TSQL. You can use this via the query window in SSMS. (Remember to connect to the correct server instance)

Method 1 : Select @@version

This will give you the following format of output:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009
10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )


Method 2 : SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

This will give you the following format of information:

10.0.1600.22

RTM

Enterprise Edition

Useful Links:

For more details and further ways to find out versions try the following link.
http://support.microsoft.com/kb/321185

Perfmon - Application error after creating new log to text file. WinXP

I created a new log within 'perfmon' which outputs to a Text File. However, when I tried to run it the application started but then terminates immediately, showing an error.

To fix this:

1. In Performance Monitor, I right-clicked the log item listed in the Counter Logs folder and selected 'Properties'.
2. On the 'Log Files' tab I removed the text from the 'Comment' text box.
3. Clicked the Apply button.

Fixed!

Types of Virtualisation

Types of Virtualisation

There are several types of 'Virtualisation' including:

• Hardware/Platform
• Software
• Memory
• Storage
• Data
• Network

Hardware Virtualisation/Platform Virtualisation:

The creation of a virtual machine (VM) that acts like a traditional computer with an operating system. Software on the virtual machines is separated from the underlying hardware resources. e.g. a computer that is running a Mac OS may host a VM that looks like a computer with a Windows operating system. The Windows OS software can be run on that virtual machine.

Software Virtualisation

• Operating system-level virtualization: hosting of multiple virtualized environments within a single OS instance
• Application virtualization and Workspace virtualization: the hosting of individual applications in an environment separated from the underlying OS

Memory Virtualisation

• Memory virtualization: aggregating RAM resources from networked systems into a single memory pool.
• Virtual memory: giving an application program the impression that it has contiguous working memory.

Storage Virtualisation

• Storage virtualization: the process of fully abstracting logical storage from physical storage
• Distributed file system.

Data Virtualisation

• Data virtualization: The presentation of data as an abstract layer which is independent of underlying database systems, structures and storage.
• Database virtualization: the decoupling of the database layer which lies between the storage and application layers within the application stack.

Network Virtualisation

• Desktop virtualization: Separating a desktop environment from its physical computer (and its OS) and storing it on another machine across a network e.g. on a center server. Thin clients also employ desktop virtualization.
• Network virtualization: The creation of a virtualized network addressing space within or across network subnets.

Import Maintenance Plan - dtsx Packages (Changing Named Instance) - SQL Server 2008

Exporting and Importing Maintenance Plan dtsx Package/s from MSDB Folder - SQL Server 2008

Troubleshooting imported dts packages. If you find that your dts package you have imported into SSMS does not work check the following:

• Compatible versions of SQL Server tools.
• The xml configuration file has been edited for the correct server instance.(see below)
• The local server connection will need to be edited within BIDS as this is not changed on import.

n.b. Due to the idiosyncracies of SQL Server versions, always use packages created and intended for 2008 versions. i.e. Created in BIDS 2008 for SQL Server 2008 databases and imported via SSMS 2008. Otherwise errors may occur such as incompatible stored procedures.

Preparing the package:

For the purpose of this guide I will assume that the Maintenance Plan package/s have already been created.

1.      Open the relevant MS BIDS 2008 project and package/s.

2.      Select a Maintenance Plan package.

3.      In the ‘Connection Managers’ section double click on the ‘Local Server Connection’ to open the ‘Connection Manager’ box.

4.      Change the ‘Server Name’ to the specific server instance you want and then click the ‘Test Connection’ button. Click OK

5.      Save the package. (Save a copy of the package/s into an accessible location where we can import them from later.

6.      Repeat this process for all of your packages.

Changing the instance name in the xml configuration file:
We now need to change the DTS .xml file for configuration to the correct named instance.

1. Stop the SSIS service.
2. Open in a text editor such as notepad the following the xml configuration file: <install location>\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml
3. To configure a named instance instead of the default instance, change the existing ServerName to ServerName\InstanceName under the Folder node of type SQLServerFolder
4. To configure a named instance in addition to the default instance, add a new Folder node of type SQLServerFolder under the TopLevelFolders node
5. Save and close the xml configuration file.
6. Restart the SSIS service

Before change:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>.</ServerName>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>

After change to a named instance:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>MyServer\MyInstance</ServerName>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>

After adding a named instance to the default:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>.</ServerName>
  </Folder>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>MyServer\MyInstance</ServerName>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>
  </Folder>

Importing the package into SSMS 2008:

·         Whilst on the Server, open SSMS for the coinciding server version i.e. 2008. Within SSMS go to ‘Connect’ and connect to ‘Integration Services’ for the relevant instance. A new node will appear for this service in the Object Explorer. 

·         Within the Stored Packages > MSDB > Maintenance Plans folder of Integration Services, right click and select ‘import packages…’ 

·         Select ‘File System’ in the ‘Package Location’ and within the ‘Package path:’ selection, choose the path on the server where you saved the packages earlier and click ok. 

·         The packages should now appear in the maintenance plans folder. (If not, refresh the maintenance plan folder – [right click > ‘Refresh’])

[If you still cannot see your package then double-check the xml configuration file you edited earlier.]

Finally:
Open up each plan/s and carry out checks as some elements do not carry across:

1. Check the correct drive letter has been used for where the backups and logs are saved to.
2. The schedule will not have been carried across – this will need to be completed manually.
3. Check the owner of each SQL Agent job.
4. Check that the correct user databases are selected.

 Useful links:
http://www.sqlservercentral.com/blogs/robert_davis/archive/2008/12/11/How-Do-I-Configure-SSIS-to-Work-With-a-Named-Instance.aspx

Basic Troubleshooting of a Slow Database - SQL Server 2008

Troubleshoot a Slow Database: SQL Server 2008

• Troubleshooting database slowness can be difficult.  Most problems will present themselves to you right away. 
• Sometimes the exact issue may not appear obvious. Furthermore once you find the issue, there isn't a satisfactory solution.

The guide below is a way of finding a place to start and will help with the majority of 'slowness' issues.

Once you have determined that it is SQL Server that is causing the performance issues, we need to find the query which is causing the problems.

Your primary performance investigations may have told you that it was one of three issues:

• A CPU issue
• A disk issue
• Memory Issue

Use the following code depending upon the issue. n.b. they are all based upon (Select * from sys.sysprocesses ) except for the Order By clause.

In SSMS, open a new query and type the following line of code:

CPU issue: "Select * from sys.sysprocesses order by cpu desc"
Disk Issue: "Select * from sys.sysprocesses order by physical_io desc"
Memory Issue: "Select * from sys.sysprocesses order by memusage desc"

Each of these order clauses tells you what the most expensive queries are in terms of what you’re ordering by.  So the “order by memusage desc” clause puts the highest memory usage queries to the top of the list. 



Further Investigations:

If the queries above do not identify any issues you’ll need to check some other things in the 'sys.sysprocesses' table.

Blocking:

In SSMS, execute the following code: "Select * from sys.sysprocesses order by blocked"

Look at the blocked column.  A small amount of blocking in your database is ok provided that these are intermittent. However, if you’ve got a single process that’s blocking everything else then this is a problem. This block will subsequently block another process, sparking a chain reaction of blocked processes. 

We need to identify the first process that is causing the chain reaction of blocked processes. It can be identified as the process that isn't blocked by anything else.

spid	blocked
101	121
114	0
121	114
150	136
136	101

The numbers in the blocked column relate to the numbers in the spid column. Thus we can see that the spid; 114 is not being blocked by anything.

You can now hand this over to your DBA. If you happen to be the DBA you can find out what that spid is doing by executing the DBCC InputBuffer(spid) in SSMS query window:

DBCC InputBuffer(spid)

To look at spid 114 type: DBCC InputBuffer(114)


"lastwaittype":
Another more complex column to look at within 'sys.sysprocesses' is 'lastwaittype'.  This can tell you which query the initial blocked spid is waiting to complete.  More can be written about this but we will keep it simple for now and just take a look at the following values.

• Cxpacket
• IO_Completion
• Network_io
• SOS_SCHEDULER_YIELD

When looking at the 'lastwaittype', these are some explanations as to some of the values:

Cxpacket – This usually means that your process is waiting on other parallel processes to complete.  So what’s probably happening is you have a process running on more than one CPU and one thread finished before the rest.  To fix this: limit your parallelism either at the query or server level.  It could also be a query tuning issue.
IO_Completion – Long wait times indicate disk issues. You could either not have enough disks for the operation or you could be running an array in degraded mode.
Network_io – If you see this for an extended amount of time, you’re waiting on the network, and this is an indication that there is too much traffic.  Solution: Call your network team.
SOS_SCHEDULER_YIELD – Long wait times indicate CPU issues.  You may not have enough CPU or you may have some queries that need tuning.

Useful Links:

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Shutdown Event Tracker - Windows Server 2003

What is the Shutdown Event Tracker on a Windows Server 2003?

To allow services, programs, and files to close correctly, you should only turn off the Server when the operating system informs you that it is OK to shut down the server.

Server administrators need to monitor when and why servers are restarted, Windows Server 2003 includes the following tools to monitor and control shutdown events:

• Shutdown Event Tracker
• Shutdown.exe

The Shutdown Event Tracker is a new Windows Server 2003 feature. It is a simple GUI application that allows Server administrators to monitor shutdown events on the server. (The tool is enabled on Windows Server 2003 by default.)

The Shutdown Event Tracker collects information on the reasons why the server was shut down, and then logs this information in Event Viewer. Shutdown events can be viewed in Event Viewer.

The Shutdown Event Tracker requires you to provide a reason whenever a server is shut down or restarted. When a server is expectedly shut down, a dialog box or page is displayed, requesting you to specify the reason for the server being shut down. When a server is unexpectedly shut down the next user to log on to the server has to specify the reason for the server shutting down.

Overview of Server Shutdown Types

Server shutdowns can be grouped into two main categories:

1. Expected Shutdowns
2. Unexpected Shutdowns

Expected shutdowns: A shutdown you predict to happen. Expected shutdowns usually occur when one of the following actions are performed:

• Clicking Start, and then the Shutdown command
• Holding down Ctrl + Alt + Del, and then clicking Shutdown

Expected shutdowns can be categorized into:

• Planned shutdowns: These are shutdowns which administrators have some form of control over
• Unplanned shutdowns: These are shutdowns normally started by applications.

Unexpected shutdowns: Server shutdowns which occur without warning. e.g. A power outage.

Useful Links:

Disable Shutdown Event Tracker - Windows Server 2003

What is an ODBC connection?

What is an ODBC connection?

Open Data Base Connectivity (ODBC). A connection that is defined and created to link between a computer and a database stored on another system. The ODBC connection contains information needed to allow a computer user to access the information stored in a database that is not local to that computer. e.g. If you are using a 3rd party reporting application (e.g. Crystal Reports/Business Objects), you can use this solution to interrogate a database by creating an ODBC connection between the two.

When you use an ODBC connection you need to:

• Define the type of database that you want to connect to - e.g. Microsoft SQL Server, Oracle, FoxPro, MYSQL etc.
• Select the appropriate driver for a connection (Windows contains many of these) and then supply the name of the database file and the credentials needed to access the database.
• Once the ODBC connection is created, you then tell system applications etc to use that ODBC connection to access information in the database.

Useful Links:

Using ODBC with Microsoft SQL Server

MySQL Connectors - ODBC

Oracle - ODBC

ODBC Reserved Keyword - SQL Server 2008

ODBC Reserved Keywords - SQL Server 2008

The following words are reserved for use in ODBC function calls in SQL Server 2008.

According to Microsoft Books On-line:  "Microsoft SQL Server reserves certain keywords for its exclusive use. For example, using the Transact-SQL BACKUP keyword in an sqlcmd or SQL Server Code Editor session tells SQL Server to make a backup copy of all or part of a database, or a backup copy of the log." http://msdn.microsoft.com/en-us/library/ms190011.aspx

This is a list of ODBC reserved keywords for SQL Server 2008.

ABSOLUTE	EXEC	OVERLAPS
ACTION	EXECUTE	PAD
ADA	EXISTS	PARTIAL
ADD	EXTERNAL	PASCAL
ALL	EXTRACT	POSITION
ALLOCATE	FALSE	PRECISION
ALTER	FETCH	PREPARE
AND	FIRST	PRESERVE
ANY	FLOAT	PRIMARY
ARE	FOR	PRIOR
AS	FOREIGN	PRIVILEGES
ASC	FORTRAN	PROCEDURE
ASSERTION	FOUND	PUBLIC
AT	FROM	READ
AUTHORIZATION	FULL	REAL
AVG	GET	REFERENCES
BEGIN	GLOBAL	RELATIVE
BETWEEN	GO	RESTRICT
BIT	GOTO	REVOKE
BIT_LENGTH	GRANT	RIGHT
BOTH	GROUP	ROLLBACK
BY	HAVING	ROWS
CASCADE	HOUR	SCHEMA
CASCADED	IDENTITY	SCROLL
CASE	IMMEDIATE	SECOND
CAST	IN	SECTION
CATALOG	INCLUDE	SELECT
CHAR	INDEX	SESSION
CHAR_LENGTH	INDICATOR	SESSION_USER
CHARACTER	INITIALLY	SET
CHARACTER_LENGTH	INNER	SIZE
CHECK	INPUT	SMALLINT
CLOSE	INSENSITIVE	SOME
COALESCE	INSERT	SPACE
COLLATE	INT	SQL
COLLATION	INTEGER	SQLCA
COLUMN	INTERSECT	SQLCODE
COMMIT	INTERVAL	SQLERROR
CONNECT	INTO	SQLSTATE
CONNECTION	IS	SQLWARNING
CONSTRAINT	ISOLATION	SUBSTRING
CONSTRAINTS	JOIN	SUM
CONTINUE	KEY	SYSTEM_USER
CONVERT	LANGUAGE	TABLE
CORRESPONDING	LAST	TEMPORARY
COUNT	LEADING	THEN
CREATE	LEFT	TIME
CROSS	LEVEL	TIMESTAMP
CURRENT	LIKE	TIMEZONE_HOUR
CURRENT_DATE	LOCAL	TIMEZONE_MINUTE
CURRENT_TIME	LOWER	TO
CURRENT_TIMESTAMP	MATCH	TRAILING
CURRENT_USER	MAX	TRANSACTION
CURSOR	MIN	TRANSLATE
DATE	MINUTE	TRANSLATION
DAY	MODULE	TRIM
DEALLOCATE	MONTH	TRUE
DEC	NAMES	UNION
DECIMAL	NATIONAL	UNIQUE
DECLARE	NATURAL	UNKNOWN
DEFAULT	NCHAR	UPDATE
DEFERRABLE	NEXT	UPPER
DEFERRED	NO	USAGE
DELETE	NONE	USER
DESC	NOT	USING
DESCRIBE	NULL	VALUE
DESCRIPTOR	NULLIF	VALUES
DIAGNOSTICS	NUMERIC	VARCHAR
DISCONNECT	OCTET_LENGTH	VARYING
DISTINCT	OF	VIEW
DOMAIN	ON	WHEN
DOUBLE	ONLY	WHENEVER
DROP	OPEN	WHERE
ELSE	OPTION	WITH
END	OR	WORK
END-EXEC	ORDER	WRITE
ESCAPE	OUTER	YEAR
EXCEPT	OUTPUT	ZONE
EXCEPTION