Featured Article(s)
A More Effective Index Rebuild/Reorganize Strategy in SQL Server
In one of companies I worked for, we used to do nightly index rebuilds. This, of course, is not efficient because rebuilds are very resource intensive. However, what was baffling is that even after the nightly rebuilds, the fragmentation for some of the tables remain high. I did some digging; here’s an explanation on why some index fragmentations remain high. Molecular MDX: Select
Part 1: Welcome to the new, SSWUG-exclusive Molecular MDX series! Join BI Architect Bill Pearson as he uses the TopCount(), TopPercent(), and TopSum() functions to select "top" elements from multiple perspectives.
Long-ish, but Helpful, Response on Performance Options
I wanted to pass along this note from Brian – he’s put some really solid thought into this and some specific tips and direction. Enjoy!
"I think you have missed the third choice. ‘Change in Algorithm.’
I’m a consultant who has spent the last ten years tuning SQL instances. Often just after they purchased new hardware and the app still failed to perform. Most gigs allowed me four hours to five days to investigate and recommend solutions. Often I’d be told where the problem was. More often than not they were only partially correct.
I’d start by running queries shown below. These would tell me if I was disk, CPU or lock bound.
Programmers almost never ‘think’ about how SQL resolves requests, which often leads to poor performance.
Simple things like indexes and application caching go a long way.
– NoLock or ReadCommitedSnapshots can really help.
– SQL 2008? Take a look at compression if you are disk bound.
More often the real solution was a Change in Algorithm:
1. One time I looked at a farm of 64 SQL servers running an application that backended to an COTS workflow engine. Servers where running hot! They were concerned would not be able to handle a new contract that would double the load. My task: Develop a performance test that would determine how the app would scale. They need to know if how many servers, 128 or 256, would be required to handle the load! I told them replace the workflow app was serious overkill. Replace it with a simple SQL proc and run on 8 servers. They were very skeptical! Two days de-integrating the workflow app and integrating a ten line sproc. Ran the app on 6 servers!
2. Another time a data warehouse type application was just dying on nightly loads. Fact and Load tables all had Guid’s and an entry_datetime. They were clustered by guid – Joins were of the form
Select * from A join B on a.guidId = b.guidid
Adding a virtual column entry_date = convert( char(10), entry_datetime, 111), changing the Clustered Index to entry_date, guid and the query to
Select * from A join B on a.entry_datetime = b.entry_datetime, a.guidId = b.guidid
Cut load times form 10 hours to 20 minutes.
3. A final example. An application storing 800,000,000 rows user action data for 120,000 users. The data was clustered by user and connection_datetime. Users connect during the workday, every 15 minutes, and we would get their most recent history.
‘Select top 32 * from user_actions where user_id = @user_id order by entry_datetime,
Our SAN was moving 80-120 MB/sec. We change the query to something like.
If ( Select count(*) from user_actions where user_id = @user_id and entry_datetime > getdate()-1) >= 32
Select top 32 * from user_actions where user_id = @user_id and entry_datetime > getdate()-1
Else
Select top 32 * from user_actions where user_id = @user_id
SAN IO dropped to 2-3 M/Sec. It really messes with operations when installed. They were sure something must be broken.
From my observations every time you double the hardware you get 80% improvement in speed. Often people forget the full support costs to purchase and install as well as user disruption.
Change the algorithm, which often require significantly less investment, you may see orders of magnitude improvements.
The only hard part, you actually need to think
— SQL_Server_Query_Stats
if object_id( ‘SQL_Server_Query_Stats’) is not null drop view SQL_Server_Query_Stats
go
Create view SQL_Server_Query_Stats as
select
[TimeInCache] = isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1),
[Recompiles] = s.plan_generation_num/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1),
[eXecutions] = s.execution_count,
[XPerMin] = s.execution_count/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1),
[XPerMin%] = case when m.execution_count = 0 then 0 else s.execution_count /isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1) * 100/m.execution_count end,
[Elapse] = s.total_elapsed_time,
[ElapseAvg] = s.total_elapsed_time/s.execution_count,
[ElapsePerMin] = s.total_elapsed_time/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1),
[ElapsePerMin%] = case when m.total_elapsed_time = 0 then 0 else s.total_elapsed_time/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1)*100/m.total_elapsed_time end,
[Cpu] = s.total_worker_time,
[CpuAvg] = s.total_worker_time/s.execution_count,
[CpuPerMin] = s.total_worker_time/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1),
[CpuPerMin%] = case when m.total_worker_time = 0 then 0 else s.total_worker_time/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1)*100/m.total_worker_time end,
[LRead] = s.total_logical_reads,
[LReadAvg] = s.total_logical_reads/s.execution_count,
[LReadPerMin] = s.total_logical_reads/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1),
[LReadPerMin%] = case when m.total_logical_reads = 0 then 0 else s.total_logical_reads/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1)*100/m.total_logical_reads end,
[PRead] = s.total_physical_reads,
[PReadAvg] = s.total_physical_reads/s.execution_count,
[PReadPerMin] = s.total_physical_reads/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1),
[PReadPerMin%] = case when m.total_physical_reads = 0 then 0 else s.total_physical_reads/isnull( nullif(datediff( minute, s.creation_time, getdate()), 0), 1)*100/m.total_physical_reads end,
[L2PReadRatio] = case when s.total_physical_reads = 0 then 0 else s.total_logical_reads/(s.total_physical_reads * 1.0) end,
[Sql] = (Select replace( substring( text, statement_start_offset/2 + 1, Case when statement_end_offset = -1 then len( convert(nvarchar(max), text)) * 2 else statement_end_offset end – statement_start_offset/2), char(13) + char(10), ‘<CRLF>’)
from sys.dm_exec_sql_text( sql_handle)
)
–,*
from
sys.dm_exec_query_stats s
cross join
(
Select
sum(execution_count/isnull( nullif(datediff( minute, creation_time, getdate()), 0), 1)) execution_count,
sum(total_elapsed_time/isnull( nullif(datediff( minute, creation_time, getdate()), 0), 1)) total_elapsed_time,
sum(total_worker_time/isnull( nullif(datediff( minute, creation_time, getdate()), 0), 1)) total_worker_time,
sum(total_physical_reads/isnull( nullif(datediff( minute, creation_time, getdate()), 0), 1)) total_physical_reads,
sum(total_logical_reads/isnull( nullif(datediff( minute, creation_time, getdate()), 0), 1)) total_logical_reads
from sys.dm_exec_query_stats
) m
go
— Aggregated WorkLoad
Select top 16 ‘Execution’, * from gwMaintenance..SQL_Server_Query_Stats order by XPerMin desc
Select top 16 ‘Durration’, * from gwMaintenance..SQL_Server_Query_Stats order by ElapsePerMin desc
Select top 16 ‘CPU……’, * from gwMaintenance..SQL_Server_Query_Stats order by CpuPerMin desc
Select top 16 ‘L Reads..’, * from gwMaintenance..SQL_Server_Query_Stats order by LReadPerMin desc
Select top 16 ‘P Reads..’, * from gwMaintenance..SQL_Server_Query_Stats order by PReadPerMin desc
— Poorly Performing Queries
Select top 16 ‘Durration’, * from gwMaintenance..SQL_Server_Query_Stats order by ElapseAvg desc
Select top 16 ‘CPU……’, * from gwMaintenance..SQL_Server_Query_Stats order by CpuAvg desc
Select top 16 ‘L Reads..’, * from gwMaintenance..SQL_Server_Query_Stats order by LReadAvg desc
Select top 16 ‘P Reads..’, * from gwMaintenance..SQL_Server_Query_Stats order by PReadAvg desc
"
Featured White Paper(s)
Java Database Connectivity
Database connections are the lifeblood of enterprise applications, administrating the secure and steady flow of information b… (read more)
Real-Time Data Integration for the SQL Server Data Warehouse
The Microsoft SQL Server database is becoming an increasingly popular platform for hosting data warehouse and operational rep… (read more)