May 16, 2012 at 12:32 pm
I will show how you could use your own optimization and statistics to tune queries which can not be tuned in other ways.
/*
I will explain how you could use custom statistics and could have your own optimizer which could perfrom better
then sql optimizer when the statistics are skewed.In these cases optimizer uses the averag stats and generate a bad plan
which is not good for all scenario.
Let us start by creating a table with skewed data. This will have one row for id 1,2 for id 2 and so on...
This table is being accessed based on id say 5 times a minute during day. The table is accessed through a proc.
*/
if OBJECT_ID('dbo.SkewedTable','U') is not null
begin
drop table SkewedTable
end
go
create table SkewedTable
( rowid int identity not null,
id int not null,
uselessdata char(2) not null
)
go
alter table SkewedTable add constraint PK_SkewedTable_Rowid primary key (rowid)
go
--this will insert around 2 million rows..
insert into SkewedTable with(tablock) (id,uselessdata)
select id.cnt as id,'UD'
from Nums id,Nums recs
where id.cnt <= 2000
and recs.cnt <= id.cnt
order by recs.cnt,id.cnt
go
create nonclustered index IX_SkewedTable_ID on SkewedTable(id,rowid)
go
--update the statistics
update Statistics SkewedTable with fullscan
go
--check the size of the table..
select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.SkewedTable','U'),null,null,null)
go
/*
Index_depth is 3 for IX_SkewedTable_ID Index_depth for Index on Primary key that is clustered index is 3 as well
Thus to access a signle row using the index IX_SkewedTable_ID will clock 6 logical IO's.
But to access the row with more data say for id 100 it will be around 3* 100 + a few logical IO for non clustered index
scan.On an average we could say that to get a row from table IOs will be number of rows retruned * 3
Table page count is 4698...
Thus threshold where the non clustered index seek + key lookup will perform better than table scan if number
of rows returned are less than 4698/3 = 1550 approx...
If rows returned are more than 1550 then table scan will perform better..This threshold and analysis will be part of your
custom statistics and your own optimizer..
Also using index scan + key lookup will cause random IO's and speed of Random IO's are less than sequential when
It will be read from disk.
*/
dbcc show_statistics ('dbo.SkewedTable','IX_SkewedTable_ID')
go
---now create the proc to access the data
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
select * from SkewedTable
where id = @id
end
go
--now execute the proc
set statistics io,time on
go
exec uspSkewedTableQuery 1
--It used just 6 IO's and used the non clustered index as expcted IX_SkewedTable_ID.
--Plan is in the cache so It will be used every time.
--now exec query with say id 100
exec uspSkewedTableQuery 100
--As expected used 303 Logical IO's and 88ms elapsed time.
--Try with 1000 rows
exec uspSkewedTableQuery 1000
--3006 IO's and 79 ms elapsed time.It has less elapsed time than 100 rows.I checked it multiple times with similar results. That is why I am
--not using elapsed time as part of my tuning criteria. I will write something on this as well some day.
--try with threshold value
exec uspSkewedTableQuery 1550
--4656 Logical IO's.It is almost same as table scan cost and 152 ms elapsed time
--Try with 1600
exec uspSkewedTableQuery 1600
--4806 Logical IO's.It is aroudn 100 more than table scan cost and 172 ms elapsed time
--Try with 2000
exec uspSkewedTableQuery 2000
--6007 Logical IO's.It is aroudn 1300 more than table scan cost and 103 ms elapsed time
--Now we will recompile the proc and call with 2000
--Try with 2000
exec sp_recompile 'SkewedTable'
exec uspSkewedTableQuery 2000
--4738 IO's and 115ms elapsed time.It used parallelism as well..
/*
So till now we tried to find what was the threshold beyond which table scan is better than index seek + lookup.
Now suppose this proc was created in proc and is executed 5 times a minute and the number of rows are quite random
If First executeion was for id 1. It will use the nested loop.Then every execution will use this plan..
Thus any call which returns more than 1570 rows will use the inefficient plan.i.e. approc 20% of total queries if we
consider call for each id will happen almost same number of times.
There is no gurantee that first call will be for id 1.What if it is for 2000 rows. It will use table scan
and so does any other call to the proc. Thus just to return 1 row it has to do around 4738 IO's. This will
cause around 80% of queries to perfoprm badly and the performance difference is way too mcuh between efficient and
and inefficient plan.
In my case the ratio is 20 to 80.It could be any ratio.
This is where we could use a better plan if we know the data very well and how that data will grow in future etc.
So what could we do to improve the perforamnce of all calls. One simpel thing comes to mind.Use recompile so that
most efficient plan is generated everytime.Let us use the recompile.
*/
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
select * from SkewedTable
where id = @id
option (recompile)
end
go
--now try some execution
exec uspSkewedTableQuery 2000 -- Nice table scan
exec uspSkewedTableQuery 100 --Nice index seek + key loopkup
exec uspSkewedTableQuery 1 --Nice index seek + key loopkup
/*
Recompile solved the problem and we are fine. Nothing needs to be done.
Let me tell you the disadvantages of the recompile. I mentioned that query is executed quite frequently aorund 5
plans per minute...Recompilations uses resources such as latches ,locks ,cpu time etc.5 recompilations per minute will
throw some of other procs out of the cache and will cause recompilation of other procs as well. Thus these could have very bad effects
on busy system with a number of users.This usually results in less scalable application.
However, there is one more issue.Some of execution might still not get the best plan.Our threshold is 1570.Above which
table scan is better than index seek + look up.So let us try with 1600.
*/
exec uspSkewedTableQuery 1600 --what happened no table scan but a index seek + key lookup.4900 IO's
--Optimizer has selected a less efficient plan.
--Now choose 1700
exec uspSkewedTableQuery 1700 -- no table scan till now around 5200 IO's
--try 1800 rows
exec uspSkewedTableQuery 1800 --still no table scan around 5500 IO's. What the hell is wrong with
--optimizer i know that it should use table scan as that needs just 4700 IO's.
exec uspSkewedTableQuery 1900 --Finally i could see table scan.Is there something wrong with the optimizer?
--Maybe or maybe not.We are just considering IO cost but optimizer uses CPU cost as well which as a developer we do not
--have any idea.But I do not know on what basis it is using the cpu cost..But doing 800 more io's in case of 1800 rows
--doesnt sound right even optimizer is using the cpu cost as well. I will write on this as well soon..If I will get something
--conclusive.
--Thus around 15% queries still did not perform as expected even with recompile.Also, it added overhead for all 100%
--queries. Only 5% queries got benefitted at the expense of all the 100% queries.
/*
What could we do to make all the queries 100% efficient? We will create our own optimizer and statistics
for our optimizer based on our query and data.How do we do that?I will show you..
As you have noticed that estimated number of rows shown when the proc was using recompile for the statement were
not near actual number of rows specially for values which return less number of rows.For 100 it was shwoing estimated rows
as 64.5 but actual rows are 100. Thus we need a way to get a better idea of the estimated rows.Based on this estimated
rows we will decide what plan to choose. Thus we need to create two tables
One will keep the estimated rows needed by us and other one will just keep one row and 2 columns.It will have threshold value
beyond which table scan is better than index seek + key lookup.In Our case it is 1570.
I am creating the table to keep threshold value so that if you need to do similarly with other tables and queries.use the same table.
*/
if OBJECT_ID('dbo.Thresholds','U') is not null
begin
drop table Thresholds
end
go
create table Thresholds
(
Tablename sysname not null,
ThresholdValue int not null
)
go
--You can create an index as well if you keep the data for more tables..
insert into Thresholds
select 'SkewedTable',1600
--truncate table Thresholds
/*
I have used 1600 as threshold value instead of 1570 as threshold value. This I have done to compensate some of the
cpu cost.
*/
go
-- You can add another column called index name as well in above table and then use the diff threshold values
--for eahc index on the table..
--Now create the our own stats table
if OBJECT_ID('dbo.SkewedTableStats','U') is not null
begin
drop table SkewedTableStats
end
go
create table SkewedTableStats
(
id int primary key not null,
Rowcnt int not null
)
go
insert into SkewedTableStats
select id,COUNT(*) from
SkewedTable
group by id
go
update Statistics SkewedTableStats with fullscan
go
--Now we got our own stats use our own optimizer which can decide what should be the
--plan based on the input param @id
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
declare @estrowcnt int,@thresholdvalue int,@actualrowcnt int
--find the number os rows returned by the given id
select @estrowcnt=Rowcnt from SkewedTableStats where id = @id
--find the threshold value
select @thresholdvalue= ThresholdValue from Thresholds where Tablename = 'SkewedTable'
--select @thresholdvalue,@estrowcnt
--now check whether @estrowcnt is greater than @thresholdvalue if @estrwocount > @threshold value use table scan
--else use index seek + key klookup
if (@estrowcnt >= @thresholdvalue)
begin
--Table scan..
select * from SkewedTable with (index =PK_SkewedTable_RowId )
where id = @id
end
else
begin
--index seek
select * from SkewedTable with (index =IX_SkewedTable_ID )
where id = @id
end
end
go
--now is time to test few scenarios
exec uspSkewedTableQuery 1
go
--Index seek 6 IO's + 1 IO
exec uspSkewedTableQuery 100
go
--Index seek 303 IO's
exec uspSkewedTableQuery 1600
go
--Table scan (clustered index scan) 4738 IO's
exec uspSkewedTableQuery 1800
go
--Table scan (clustered index scan) 4738 IO's
exec uspSkewedTableQuery 2000
go
--Table scan (clustered index scan) 4738 IO's
/*
Great we got the plan we wanted for all scenarios yet no recompiles.Just by using our little knowledge about the data
and how it is used.
This is great but it has overhead as well which needs to be considered. What are these overheads?
1. We are querying two extra tables. Innout case this is just 3 IO's which is negligible and mostly it will be negligible
only. Thus you can ignore this kind of overhead.
2. Thresholds table updates.It has the threshold value this will remain same most of time except when the index depth
of the index used will be changed this needs to be updated. Thus this table needs very few updates.Thus You can update
it during weekend or once a week on a time when there are not much activity. This can be ignored as well.
3. Stats table. This table have little bit overhead as the data in the main table will be changed this table needs to be updated.Below are possible ways to update this table
and the overheads associated with it.
a. If the data in the table changes in similar proportions then you do not need to update the table real time.Rather update once a day.
THus overhead is to populate it everyday with latest data. This is also negligible most of times unless the index size on tables is too huge.
b. If the data changes randomly and there are lots of fluctuations then you can have a trigger to update the stats table. Even auto stats will have similar kind of
overhead.
c. This is my favourite way to update the stats table with almost negligible overhead in case when data fluctuates too much.But this could be used with specific
kidn of queries and not always.
e.g. in our proc we can use this strategy as the final query will return actual number of rows in the table.If there were sarg on final query which restricts the
result set we can not use this strategy.
if OBJECT_ID('dbo.uspSkewedTableQuery','P') is not null
begin
drop procedure uspSkewedTableQuery
end
go
create procedure uspSkewedTableQuery
(
@id int
)
as
begin
declare @estrowcnt int,@thresholdvalue int,@actualrowcnt int
--find the number os rows returned by the given id
select @estrowcnt=Rowcnt from SkewedTableStats where id = @id
--find the threshold value
select @thresholdvalue= ThresholdValue from Thresholds where Tablename = 'SkewedTable'
--select @thresholdvalue,@estrowcnt
--now check whether @estrowcnt is greater than @thresholdvalue if @estrwocount > @threshold value use table scan
--else use index seek + key klookup
if (@estrowcnt >= @thresholdvalue)
begin
--Table scan..
select * from SkewedTable with (index =PK_SkewedTable_RowId )
where id = @id
set @actualrowcnt = @@rowcnt
--if actual row count falls below threshold than update the actual rowc ount in stats table.
if (@actualrowcnt < @thresholdvalue)
begin
update SkewedTableStats
set rowcnt = @actualrowcnt
where id= @id
end
end
else
begin
--index seek
select * from SkewedTable with (index =IX_SkewedTable_ID )
where id = @id
set @actualrowcnt = @@rowcnt
if (@actualrowcnt >= @thresholdvalue)
begin
update SkewedTableStats
set rowcnt = @actualrowcnt
where id= @id
end
end
end
go
So this has minimum overhead and kind of resemble the async update stats configure parameter of SQL server
I have mostly used above 3 methods to update my stats table.Specially 1 and 3.
4. In SQL server you could use an indexed view instead of the stats table. It will have its overhead as well.However, if you have indexed view on this table
and which uses the aggregate function for some other purpose in your application. Then this is the best way as you will have almost minimal overhead.
As you have seen that using your own optimizer and stats have some overhead. Thus before using this strategy make sure that the gain in performance is more than
the overhead associated with it. If your stats table could be updated using methods in 1,3 and 4. Then it has almost no overhead.
There are cases when you do not need stats table at all in that case you can use this strategy without any overhead. I will explain this in next post.
Fine.What kind of scenario we can use our own stats and optimizer.
1. In the case when the column (s) data is too much skewed and almost all the values in the column are used to get the data from the table.e.g. in this case.
2. When the column data is distributed almost equally but your queries used < ,> or between instead of equal. This strategy could be used to get best plan.
3. This strategy deciding your own plan based on stats will eliminate parameter sniffing (however, you will still see the estimated number of rows etc based on first
execution but performance wise parameter sniffing is almost eliminated). Thus if you have parameter sniffing try to use this to eliminate.
4. This can be used when you get the data ito some intermediate table like temp table and then you get the data from your main tables based on that temp table.
I will explain this in next post. This doesnt need a stats table at all.
Conclusion: You can have your own optimizer and stats to make sure that you get the best plan. This depends mainly on the data you have,how this data is accessed etc.
*/
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply