Rebuild SQL indexes (T-SQL)

Posted: April 6, 2011 in SQL

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = ‘base table’
AND table_schema=’dbo’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

EXEC sp_updatestats

so , there are some datafiles that we can take offline with minimal impact

1. alter database datafile ‘full_path’ offline;
2. at the OS level do a copy
3. cp  -p  source_path   target_path
4. recover datafile ‘target_path’
5. alter database datafile ‘target_path’ online

Some basics

Posted: April 5, 2011 in Uncategorized

Linux mail (sendmail)
———-
echo " " | mail -s "test from `uname -n`" bruce.mcfarlane@tangoe.com
                                                      or text@txt.att.net

Check Filesystem
—————-
df -h

Check running DB processes
————————–
ps -ef|grep smon

Check mail queue
—————-

mailq or sendmail -bp
/var/spool/mqueue/

A New Direction

Posted: April 5, 2011 in Rant

So recently I was hired at a new company as a DBA….but not just for my SQL knowledge…..for ORACLE.

I know….it is rather odd considering the normal flow of mutiny goes from Oracle > MS SQL but I am so far enjoying the challenge.  I am going to start posting quite a bit of information on here solely for my reference and I’m hoping that it may help anyone that may stumble across this page as well.

How to retrieve a cached SQL query plan.

Posted: January 28, 2011 in SQL

So about 3 days ago our student classroom portal decided to spike both memory and cpu at around 9pm.  The spike only lasted a few minutes but it was enough for a few disgruntled phone calls.  Now the obvious reaction to this is “gah….I wish I had been collecting data!…..what now???”

Sys.dm_exec_query_stats is what now.  Sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans.  This allowed me to pinpoint the query that was run at the time of the resource spike.  Below is the code I used.

SELECT * FROM sys.dm_exec_query_stats

WHERE last_execution_time between ‘2011-01-25 21:15:57.870’ AND ‘2011-01-25 21:22:57.870’

AND total_logical_reads > 0

ORDER BY total_logical_reads desc

GO

The reason I used the column “total_logical_reads” is because I noticed on my SCOM interface that there was a large spike in disk activity at the same time as the memory and cpu spike.

Once I narrowed down which query (mind you I don’t actually have the query syntax yet, just the statistics of the query) that was most likely to coincide with the event, I took down the information in the “sql_handle” field and moved on to the next dm Sys.dm_exec_text_query_plan.

Next, I use the string from the “sql_handle” field as the argument for querying Sys.dm_exec_text_query_plan.

USE master;

GO

SELECT query_plan

FROM sys.dm_exec_text_query_plan (0x06000500163A5F384063F32A010000000000000000000000,0,-1);

GO

And now, I have the query plan!!!!  Granted it’s a bit detailed but the actual query used isn’t too hard to spot as its in the first couple of lines.

So I have found an unusual amount of indexes that are duplicates named “missing_index_###”.  This has already caused significant delay in a frequently and heavily used DB so I decided to seek and destroy all these duplicates.  Below is the code I used.

 

SELECT ‘DROP INDEX ‘ + quotename(name) + ‘ ON ‘ + quotename(object_schema_name(object_id)) + ‘.’ + quotename(object_name(object_id))

FROM sys.indexes

WHERE is_primary_key = 0 AND is_unique_constraint = 0

AND name like ‘missing%’

SQL Disk Best Practices

Posted: November 30, 2010 in Uncategorized

Taken from http://technet.microsoft.com/en-us/library/cc966534.aspx

Storage Top 10 Best Practices

Published: October 17, 2006

Proper configuration of IO subsystems is critical to the optimal performance and operation of SQL Server systems. Below are some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server.

1 Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application.

In order to be successful in designing and deploying storage for your SQL Server application, you need to have an understanding of your application’s IO characteristics and a basic understanding of SQL Server IO patterns. Performance monitor is the best place to capture this information for an existing application. Some of the questions you should ask yourself here are:

  • What is the read vs. write ratio of the application?

  • What are the typical IO rates (IO per second, MB/s & size of the IOs)? Monitor the perfmon counters:

    1. Average read bytes/sec, average write bytes/sec

    2. Reads/sec, writes/sec

    3. Disk read bytes/sec, disk write bytes/sec

    4. Average disk sec/read, average disk sec/write

    5. Average disk queue length

  • How much IO is sequential in nature, and how much IO is random in nature? Is this primarily an OLTP application or a Relational Data Warehouse application?

To understand the core characteristics of SQL Server IO, refer to SQL Server 2000 I/O Basics.

2 More / faster spindles are better for performance

  • Ensure that you have an adequate number of spindles to support your IO requirements with an acceptable latency.

  • Use filegroups for administration requirements such as backup / restore, partial database availability, etc.

  • Use data files to “stripe” the database across your specific IO configuration (physical disks, LUNs, etc.).

3 Try not to “over” optimize the design of the storage; simpler designs generally offer good performance and more flexibility.

  • Unless you understand the application very well avoid trying to over optimize the IO by selectively placing objects on separate spindles.

  • Make sure to give thought to the growth strategy up front. As your data size grows, how will you manage growth of data files / LUNs / RAID groups? It is much better to design for this up front than to rebalance data files or LUN(s) later in a production deployment.

4 Validate configurations prior to deployment

  • Do basic throughput testing of the IO subsystem prior to deploying SQL Server. Make sure these tests are able to achieve your IO requirements with an acceptable latency. SQLIO is one such tool which can be used for this. A document is included with the tool with basics of testing an IO subsystem. Download the SQLIO Disk Subsystem Benchmark Tool.

  • Understand that the of purpose running the SQLIO tests is not to simulate SQL Server’s exact IO characteristics but rather to test maximum throughput achievable by the IO subsystem for common SQL Server IO types.

  • IOMETER can be used as an alternative to SQLIO.

5 Always place log files on RAID 1+0 (or RAID 1) disks. This provides:

  • better protection from hardware failure, and

  • better write performance.
    Note: In general RAID 1+0 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the HW vendor’s RAID implementations. Most common alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than any other RAID level providing data protection, including RAID 5.

6 Isolate log from data at the physical disk level

  • When this is not possible (e.g., consolidated SQL environments) consider I/O characteristics and group similar I/O characteristics (i.e. all logs) on common spindles.

  • Combining heterogeneous workloads (workloads with very different IO and latency characteristics) can have negative effects on overall performance (e.g., placing Exchange and SQL data on the same physical spindles).

7 Consider configuration of TEMPDB database

  • Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.

  • Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).

  • For the TEMPDB database, create 1 data file per CPU, as described in #8 below.

8 Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.

  • It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.

  • This is especially true for TEMPDB where the recommendation is 1 data file per CPU.

  • Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

9 Don’t overlook some of SQL Server basics

  • Data files should be of equal size – SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.

  • Pre-size data and log files.

  • Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.

10 Don’t overlook storage configuration bases

  • Use up-to-date HBA drivers recommended by the storage vendor

  • Utilize storage vendor specific drivers from the HBA manufactures website

  • Tune HBA driver settings as needed for your IO volumes. In general driver specific settings should come from the storage vendor. However we have found that Queue Depth defaults are usually not deep enough to support SQL Server IO volumes.

  • Ensure that the storage array firmware is up to the latest recommended level.

  • Use multipath software to achieve balancing across HBA’s and LUN’s and ensure this is functioning properly

  • Simplifies configuration & offers advantages for availability

  • Microsoft Multipath I/O (MPIO): Vendors build Device Specific Modules (DSM) on top of Driver Development Kit provided by Microsoft.