it might be the indexes, it might be the procedure

I received some excellent EE help from kselvia, I tried to revisit this with him, but I it seems he may be unavailable and this project is critical., I must resolve.  In short, we do electronic trading.  This solution houses what we call latency messages   it is used as a viewpoint on all the trading traffic within our infrastructure, such that we can look at the traffic and identify/resolve any latencies, as they occur
you know.... why is trading taking so long on that endpoint and that client?  that sort of thing

Within the structure Ive got two tables -- the 'maintable' holds all latency messages for orderno's across the varying msgSources (exchange logs, gateway logs, pricefeed logs, etc.)  It houses an average of 180 or so million records at a time, 7 days of data.   the format is simple:

ID   int/identity value
TSdate  smalldatetime
OrderNo varchar(100)
MsgSource char(1)
Latency decimal 17(30,0)
LatencyType char(2)

sample record:  653610778   2008-02-06 00:00:00      05Grsmd57      A   17000      OA

These are the indexes:   idx1, nonclustered, LatencyType, Latency(-)
                                        idx2, nonclustered, OrderNo, TSDate, MsgSource
                                        idx3, nonclustered, TSDate, LatencyType, Latency(-)
                                        idx4, nonclustered, TSDate, OrderNo, Latency
                                        idx5, nonclustered, LatencyType, MsgSource, TSDate, Latency(-)

The 2nd table is the 'maptable'.  It holds an avg of 3M or so records at a time, only a single day.  format is simple:

MsgDate   smalldatetime
OrderNo   varchar(100)
EndPoint varchar(8)
Client varchar(32)

sample record:   2008-02-06 00:00:00     06Grtdfw4        AAAA      MA1.MARSPY      

These are the indexes:    idx1,  nonclustered, client
                                         idx2,  nonclustered, endpoint
                                         idx3,  clustered unique pk on OrderNo

Unfortunately, there is no way to get the EndPoint and/or Client into the maintable, so I have to join to the maptable on orderno, such that i can get the latency statistics out by endpoint and/or client.

Given the volume of data, my indexes are paramount .  Right now, I THINK my indexes on the maintable are sufficient, but I am questioning whether the maptable is indexed properly.  

The ONE procedure that accesses this data does a join on the two tables  it is dynamic:

CREATE procedure PROCNAME (
@tsDate datetime = null,
@latencytype char(50) = null,
@topcount int = 50,
@msgsource char(1)=null,
@endpoint varchar(8)=null,
@client varchar(32)=null
)
as
set nocount on
 
declare @chardate varchar(50)
declare @sql varchar(8000)
 
if @tsdate is null
   select @chardate = convert(varchar(11), DATEADD(day, DATEDIFF(day, 0,max(tsDate)), 0))
from database.dbo.maintable
else
   select @chardate = convert(varchar(11), DATEADD(day, DATEDIFF(day, 0,max(@tsDate)), 0))
 
If @latencytype is null set @latencytype = 'OA,OT,OJ,XC,XJ,XT,XJ,RA,RT,RJ'
 
select @sql = IsNull(@sql+' UNION ' + char(10),'') +
      'select top '+convert(varchar,@topcount)+ IsNull (' Endpoint=''' + @endpoint + ''',','') + IsNull ( ' Client='''+@client+ ''',','') +
      ' e.OrderNo,MsgSource,Latency,LatencyType,tsDate from database.dbo.maintable e WITH (NOLOCK)' +
      Case When @endpoint Is Not Null Or @client Is Not Null
            Then 'INNER JOIN database.dbo.maptable m ON e.orderno = m.orderno '
                  + IsNull ( ' AND m.EndPoint = ''' + @endpoint+'''', '' ) + IsNull (' AND m.Client = ''' + @client +'''' , '' )
      Else ' ' End  +
      ' where LatencyType =''' + substring(@latencytype, number * 3 + 1, 2) + '''' +
      IsNull ( ' and tsDate >= ''' + @chardate + ''' ' , '' ) +
      ISNULL(' and MsgSource='''+@msgSource+'''','') + ' ORDER BY Latency DESC '
From master..spt_values where type = 'P' and number < (len(@latencytype) + 1) / 3
 
Select @sql = 'SELECT * FROM ( ' + @sql + ') d ORDER BY ' + case when len(@latencytype) = 2 then ''
Else 'LatencyType, ' end + ' Latency DESC'
 
exec (@Sql)
--print (@Sql)
GO

This proc makes the stats available thru a front end  initially, they were all flying so we deployed, and now some are done instantly, others never complete.  See here, sample execs, runtimes on each (note those I killed):

exec procedurename @tspdate='2/5/08',@msgsource='A' --:08
exec procedurename @msgsource='A' --:08
exec procedurename @latencytype = 'oa' -- killed at 2:59
exec procedurename @tsdate='2/05/08',@latencytype = 'oa' --:00
exec procedurename @tsdate=null,@latencytype = 'oa' --killed at 2:26
exec procedurename @tsdate='2/6/08',@latencytype = 'oa',@client='dowbigs'--killed at 3:19
exec procedurename @tsdate='2/4/08' -- :01
exec procedurename @tsdate='2/5/08',@latencytype = 'oa',@client='dev1'--:00
exec procedurename @tsdate='2/6/08',@latencytype = 'oa',@client='dev1',@msgsource='a' -- :00
exec procedurename @latencytype='OA', @topcount=100, @msgsource='D' -- 1:18
exec procedurename @endpoint='aaaa',@sdate='2/5/08'--:32


I realize there are a number of other details out there... but, just given this, does anybody have any advice or suggestions on how I can improve procedure runtime?  Is it the proc, or the indexes, or both?  Is anyone able to advise?  Are there any other details I can provide? (i.e., showplan output, print @sql,?)

I know you hear this a lot, but it truly is quite urgent.  Like I said, initially it all was beautiful  instantaneous results on every procedure execution.  So, we deployed.  Now its out there, and they cant use it.  I really am grateful for any direction or insight at all.

Any thoughts at all?
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Have the statistics been maintained/updated recently? How often do you run a dbcc dbreindex?

HTH
  David
0
dbaSQLAuthor Commented:
I'm sorry, I left out that information --- yes, i update statistics on a daily basis.  the scan density is literally at 100% (see below).  To my knowledge, there isn't any fragmentation here at all.  This repository is used for nothing except what I have detailed --- sure, sure there are other intended uses, but we won't get there if I can't get beyond this problem.  

this is the maintable:
TABLE level scan performed.
- Pages Scanned................................: 1317402
- Extents Scanned..............................: 164677
- Extent Switches..............................: 164676
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [164676:164677]
- Extent Scan Fragmentation ...................: 0.11%
- Avg. Bytes Free per Page.....................: 43.3
- Avg. Page Density (full).....................: 99.47%

this is my map table:
TABLE level scan performed.
- Pages Scanned................................: 16951
- Extents Scanned..............................: 2134
- Extent Switches..............................: 2133
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.30% [2119:2134]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.14%
- Avg. Bytes Free per Page.....................: 12.6
- Avg. Page Density (full).....................: 99.84%
0
David ToddSenior DBACommented:
Hi,

I've taken a second look at the body of your procedure.

Dynamic SQL has a number of issues - security, plan reuse, index usage ...

I'd like you to consider the query below.

I wasn't too sure of the relevance of this line
From master..spt_values where type = 'P' and number < (len(@latencytype) + 1) / 3

What I've done
- ignored the variable for top.
- used ( @endpoint is null or mi.endpoint = @endpoint ) construct for most of your inputs.
- used a left outer join as I wasn't sure what the inner join would do with some of your inputs and the way I was changing your query.

Cheers
  David


select top 50
	@endpoint as Endpoint
	, @client as Client
	, e.OrderNo
	, e.MsgSource
	, e.Latency
	, e.LatencyType
	, e.tsDate
from dbo.maintance e with (nolock)
left outer join 
	(
	select *
	from dbo.maptable mi
	where
		( @endpoint is null or mi.endpoint = @endpoint )
		and ( @client is null or mi.Client = @client )
	) m
	on e.orderno = m.orderno
where 
	e.tsDate >= @tsDate
	and ( @MsgSource is null or e.MsgSource = @MsgSource )
order by
	case
		when len( @latencyType ) = 2 then ''
		else e.latencyType
	end
	, e.Latency desc

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

dbaSQLAuthor Commented:
i'm not sure. i've declared each of them and set @endpoint and @tsdate, it's still running at 2:51
0
David ToddSenior DBACommented:
Hi,

When you say 2:51 do you mean almost 3 hours or almost 3 minutes?

In your procedure, can you uncomment the print @SQL line, and for some of the queries that run fast, capture that output and paste it in Query Analyser. Press Ctrl + L to get the estimated query plan. Mouse over the individual items and see what the cost and subtree cost are.

What index is being used?

Compare with one of the queries that runs slowly.

My guess is that adding an index on orderNo to Maintable might help. The join is possibly where the holding-up is ...

Cheers
  David
0
dbaSQLAuthor Commented:
i mean almost 3 minutes.  
i have two composite indexes on orderno -- do you mean one only on orderno by itself?

idx1, nonclustered, LatencyType, Latency(-)
idx2, nonclustered, OrderNo, TSDate, MsgSource
idx3, nonclustered, TSDate, LatencyType, Latency(-)
idx4, nonclustered, TSDate, OrderNo, Latency
idx5, nonclustered, LatencyType, MsgSource, TSDate, Latency(-)

showplan output says i am using idx3 pretty much for everything.  
it does, however, have a clustered index seek on this argument:

OBJECT:([master].[dbo].[spt_values].[spt_valuesclust]), SEEK:([spt_values].[type]='P' AND [spt_values].[number] < (len(Convert([@latencytype]))+1)/3) ORDERED FORWARD


estimated execution plan in query analyzer pretty much says the same -- it gives 65% of the cost to and index scan on idx3
and 34% of the cost to a clustered index seek on master.dbo.spt_values.sptvaluesclust
0
David ToddSenior DBACommented:
Hi,

The core problem here is the scan. Scans are bad.

If you created a profile trace of these queries, and from profiler used the index tuning wizard, is it going to suggest additional indexes?

Overall, here is my suggestion: If the key problem is the table join, then write one procedure as I indeicated without the dynamic SQL but using the @variable is null or field = @variable technique, and get that working. That will give many results quite quickly.

Then write the remaining queries and options into another procedure.

I dont understand what you are doing with the maths on the latency type, but it appears to be quick at this stage.

Cheers
  David
0
dbaSQLAuthor Commented:
I have used the index tuning wizard repeatedly.  But, I assume I am doing it wrong as every single attempt to do so tells me no indexes are recommended.  

>>>Overall, here is my suggestion: If the key problem is the table join, then write one procedure as I indeicated without the dynamic SQL but using the @variable is null or field = @variable technique, and get that working. That will give many results quite quickly.

Then write the remaining queries and options into another procedure.

I don't know if the problem is the join, but if it is, I am not sure what you are suggesting.  Can you show me an example?  The previous tsql you posted never completed.  I killed  it at 7 minutes.  

Also please note the runtimes I posted before.  ALL of them are using the join, some come back at :00 runtime, some at :01... others not at all.  I am not sure, thoughk, how to get around this without the dynamic tsql.  can you please provide more of an example?
0
David ToddSenior DBACommented:
Hi,

Two procedures below.

First one - procname2 - doesn't take endpoint or client as inputs. My guess is that this procedure will run quite fast.

What version of SQL are you using? Currently I'm assuming MS SQL 2000.

Cheers
  David
create procedure procname2 (
	@tsDate datetime = null
	, @latencytype char(50) = null
--	, @topcount int = 50
	, @msgsource char(1) = null
)
as
	set nocount on
	
	-- No EndPoint, No Client
 
	select @tsdate = dateadd( day, datediff( day, 0, isnull( @tsdate, max( mt.tsDate ))), 0 ))
	from dbo.maintable mt
 
	select top 50
		e.OrderNo
		, e.MsgSource
		, e.Latency
		, e.LatencyType
		, e.tsDate
	from 
		(
		select
		ei.OrderNo
		, ei.MsgSource
		, ei.Latency
		, ei.LatencyType
		, ei.tsDate
		from dbo.maintance ei with (nolock)
		where
			ei.tsDate >= @tsDate
			and ( @MsgSource is null or ei.MsgSource = @MsgSource )
		) e
	order by
		case
			when len( @latencyType ) = 2 then ''
			else e.latencyType
		end
		, e.Latency desc
 
create procedure procname (
	@tsDate datetime = null
	, @latencytype char(50) = null
--	, @topcount int = 50
	, @msgsource char(1) = null
	, @endpoint varchar(8) = null
	, @client varchar(32) = null
)
as
	set nocount on
 
	select @tsdate = dateadd( day, datediff( day, 0, isnull( @tsdate, max( mt.tsDate ))), 0 ))
	from dbo.maintable mt
 
	select top 50
		@endpoint as Endpoint
		, @client as Client
		, e.OrderNo
		, e.MsgSource
		, e.Latency
		, e.LatencyType
		, e.tsDate
	from 
		(
		select
		ei.OrderNo
		, ei.MsgSource
		, ei.Latency
		, ei.LatencyType
		, ei.tsDate
		from dbo.maintance ei with (nolock)
		where
			ei.tsDate >= @tsDate
			and ( @MsgSource is null or ei.MsgSource = @MsgSource )
		) e
	inner join
		(
		select
			mi.OrderNo
		from dbo.maptable mi
		where
			( @endpoint is null or mi.endpoint = @endpoint )
			and ( @client is null or mi.Client = @client )
		) m
		on e.orderno = m.orderno
	order by
		case
			when len( @latencyType ) = 2 then ''
			else e.latencyType
		end
		, e.Latency desc

Open in new window

0
David ToddSenior DBACommented:
Hi,

Can you post results from

set showplan_text on
go

exec procname2 @tsdate = '2008-02-04'
go

set showplan_text off
go

Cheers
  David
0
dbaSQLAuthor Commented:
It's very robust, david.  You sure you want it here?
0
David ToddSenior DBACommented:
Hi,

Post as a snippet ...

Cheers
  David
0
dbaSQLAuthor Commented:
embarrasingly so, i'm not sure what that means, david.  a 'snippet'  ?  it's pretty big, shall i just post it all herein?
0
David ToddSenior DBACommented:
Hi,

I have in the comment below check boxes for attach Code Snippet, or Attach File.

If its large, then attach as a file ...

Cheers
  David
0
dbaSQLAuthor Commented:
oh wow.  i've never used either before.  right in front of me... hmmmm......
file attached
showplan-text.txt
0
David ToddSenior DBACommented:
Hi,

I was hoping to see output from the procedure I suggested, not your original ...

Cheers
  David
0
dbaSQLAuthor Commented:
oh gosh, give me a sec
0
dbaSQLAuthor Commented:
Server: Msg 170, Level 15, State 1, Procedure procname2, Line 11
Line 11: Incorrect syntax near ')'.


it's the select @tsdate, but i don't see it.  do you?
0
David ToddSenior DBACommented:
Hi,

      select @tsdate = dateadd( day, datediff( day, 0, isnull( @tsdate, max( mt.tsDate ))), 0 )
      from dbo.maintable mt

I've removed the last ) ...

What I get for not counting my brackets :-)

Cheers
  David
0
dbaSQLAuthor Commented:
much smaller
showplan-text.txt
0
David ToddSenior DBACommented:
Hi,

Near the bottom of the text is this:

                           |--Table Scan(OBJECT:([database].[dbo].[maintable] AS [ei]), WHERE:(Convert([ei].[tsdate])>=[@tsdate] AND ([@msgsource]=NULL OR [ei].[MsgSource]=[@msgsource])))

Table scans are bad. Slowest data retreval ...

I'm puzzled why there is a convert on ei.tsdate >= @tsdate. Maybe change the parameter to be a smalldatetime instead of a datetime.

See if that query will get anything out of the Index Tuning Adviser. Given that there is a table scan, and you have an index, something is happening that is causing the query to not use that index. Try a simple index on just the tsdate column, and see if that helps any.

Cheers
  David
0
dbaSQLAuthor Commented:
yes, but i don't have a table scan on my proc, david.  do you feel i should just pursue procname2 as if it is replacing mine?  and if so, there are a number other parameters i haven't accounted for yet.  what do you think?
0
David ToddSenior DBACommented:
Hi,

Given that you are using dynamic SQL, you would have to get the procedure to output the query text, copy and paste that text into another query window, remove the double single quotes, then get the query plan and check for table scans. There is more info in the graphical estimated execution plans than in the text ones we've been using just now.

And the Index Tuning wizard struggles with dynamic SQL.

And the query optimiser can struggle with dynamic SQL.

The idea was to change the dynamic SQL and see what the query plans were, see what the index tuning wizard advises. A change to the query can assist the wizard in making better sense of the query.

Do test against a database the same size ... as the numbers of records you are talking about can have their own issues.

Are you developing on SQL Developer and deploying against SQL Standard? The tunings on each can be different!!!!

HTH
  David
0
dbaSQLAuthor Commented:
same darned thing.  i select the tsql in query analyzer, go up top choose 'index tuning wizard'
it pops up the dialgogs, i select thorough, the two tables and let it do its analysis.  i thought for sure i was going to get something... as it said 'determining 1 possible column indexes..'
then 'determining 4 possible column indexes'

then it's all done.  with 'no index recommendations for the workload and the chosen parameters'
every single variation of my procedure execs gave me the same thing.

am i doing the ITW workload incorrectly?
0
dbaSQLAuthor Commented:
>>The idea was to change the dynamic SQL and see what the query plans were, see what the index tuning wizard advises. A change to the query can assist the wizard in making better sense of the query.

That makes sense.   But, ITW gave me the same thing it gave before --- no recommendations.

I need to get some rest, I'll be back at this first thing in the morning.  Please let me know your thoughts, and possibly how you'd suggest I move forward.

thank you in advance
0
dbaSQLAuthor Commented:
Any thoughts, David?  With regard to my ITW results?  Or how I should move forward?
0
David ToddSenior DBACommented:
Hi,

What version of SQL are you using, and what edtion?

Check that all where clauses are sargable. I'll try to find a good reference on sargable, but the short version is that they must be easily searchable for the indexes.

Check the query plans, and see what indexes are being used.

Has your SQL got the latest service packs?

The later versions of SQL have better query optimisers, so consider upgrading to SQL 2005.

HTH
  David
0
dbaSQLAuthor Commented:
SQL v2000 Enterprise Edition, sp4 -   8.00.2039
Understood, I know what sargable means, and yes, they are.
v2005 upgrade is not an option right now -- not for some time, actually.

see here --

exec procedurename @latencytype='OA', @topcount=100, @msgsource='D'
this completes in 3:45.
i run it again IMMEDIATELY therafter, it completes in :16.
repeatedly.  it even gets to :01

the other executions behave similarly -- real good, real bad, then good again.

I know the optimizer may have a hard time w/dynamic.  and, all 6 of my parms are optional -- i believe maybe for this reason i am unable to re-use the exec plan???

- every effort in ITW tells me no indexes are recommended.  
- the proc in one run is perfect, beautiful
- in the next, i get the same results, 10 minutes later
- i have no fragmentation whatsoever, statistics are good, there is NOTHING else on this box but me, so nothing to conflict with, per say, in terms of concurrency

right now i am trying to simplify things by getting rid of the dynamic.  i don't know if that is the problem, but for the life of me, I cannot determine any other reason why the execution of this procedure fluctuates so radically

possibly it will be one proc without client and endpoint, as this is the only reason i join to that other table
and then one proc to handle any client/endpoint logic

what do you think?

0
David ToddSenior DBACommented:
Hi,

I think that you are on the right path - getting to two procs -  as the join seems to be where the slower runs are in the main - based on what you've posted above that is.

If we've hammerred the queries to death almost, then there are three main possibilities:
- there is something else running on the box that is interferring. Virus checker,
- not enough memory so caching works only sometime
- fault in hard drive system

In Query Analyser, there is an option under the query menu to display actual execution plan. While you are testing, I suggest having this option on, and run profiler as well. See if the difference in run times is reflected by a difference in the execution plan, the number logical vs physical reads.

Look at Brad's "How to Perform a SQL Server Database Performance Audit"
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx

Cheers
  David
0
dbaSQLAuthor Commented:
If we've hammerred the queries to death almost, then there are three main possibilities:
- there is something else running on the box that is interferring. Virus checker,    NOTHING
- not enough memory so caching works only sometime    4G
- fault in hard drive system           HAVEN'T CHECKED THIS, EVERYHTING ELSE WORKS PERFECTLY

i need to just try the non-dynamic two procs, can only do that and go from there, i guess
0
dbaSQLAuthor Commented:
You know what baffles me --- I am assuming it's a matter of the optimizer struggling with the dynamic.  For what other reason would it take 10 minutes to complete one time, and then seconds later it will complete in :01 or :02 seconds?  right?  so, i'm trying to non-dynamic the logic.... struggling a bit syntactically, but it's coming together.  and then, just out of curiousity, i profile the darn thing.  I figure if it's recompiling or missing the cache, the profiler is going to tell me so.

i take one of my execs:  exec procedurename @endpoint='aaaa',@timestampdate='2/5/08'

the proc itself took 1:10 to complete -- TONS OF sp:stmtstarting, but no recompiles, no reference to cache in the eventclass at all.  There's a ton of non-relevance in my output, so i put into the filter on textdata where not like '%sp_sqlagent%', you know, just to minimize my resultset.  I run it again, the very same PRECISE exec statement.  it completes in :02.

My profiler output --

SQL:BatchStarting  exec procedurename @endpoint='aaaa',@timestampdate='2/5/08'
SP:Starting              exec procedurename @endpoint='aaaa',@timestampdate='2/5/08'
SP:StmtStarting       set nocount on
SP:StmtStarting       if @timestampdate is null
SP:StmtStarting      select @chardate = convert(varchar(11)..............
SP:StmtStarting      if @latencytype is null
SP:StmtStarting      select @sql = ................ big statement
SP:StmtStarting      select @sql = 'SELECT * FROM ('+@sql+') d ORDER BY...................
SP:StmtStarting     exec (@sql)
SP:StmtStarting    great big select for aaaa endpoint unioned 10 times for each latencytype
SP:Completed       exec procedurename @endpoint='aaaa',@timestampdate='2/5/08'


No cache hits, misses, nothing.  So I think maybe my filter is bad -- I pull the filter, I put in the entire 'Stored Procedure' event class.  The exec again completes in :02, the only 'new' output I have in the profileer is SP:ExecContextHit.  

Does anybody have any advice at all?  This has become terribly pressing.  I just don't understand what is causing such erratic behavior in the procedure.
0
dbaSQLAuthor Commented:
question #1 -- my SP:CacheMisses -- on dbid 0, as opposed to the user database id ?  what is dbid '0' ?
question #2 -- my recompiles -- 3, Object not found at compile time, deferred check to run time.  
               there are no temp tables, all of my objects are referenced by owner, why is it not found?
 
this one takes minutes first time, completes in seconds all subsequent executions, no cachemisses
exec database.dbo.procedurename @endpoint='aaaa',@timestampdate='2/5/08'
 
this one takes 2:08 first time, 14 seconds each subsequent execution, one cachemiss:
exec database.dbo.procedurename @latencytype='OA', @topcount=100, @msgsource='D'
 
this one takes whole bunch of cachehits, cachemisses, cacheinserts, execontexthits and TONS of CacheRemoves, killed at 40 minutes:
exec database.dbo.procedurename @timestampdate='2/6/08',@latencytype = 'oa',@client='ddddd'
 
killed at 4 minutes:
exec database.dbo.procedurename @timestampdate=null,@latencytype = 'oa'
 
these complete immediately
exec database.dbo.procedurename @timestampdate='2/6/08',@latencytype = 'oa',@client='dev1',@msgsource='a'
exec database.dbo.procedurename @timestampdate='2/5/08',@latencytype = 'oa',@client='dev1' (yesterday never completed)
exec database.dbo.procedurename @timestampdate='2/4/08'
exec database.dbo.procedurename @timestampdate='2/05/08',@latencytype = 'oa'  -- cache miss, hit and insert ??


any thoughts at all?
0
dbaSQLAuthor Commented:
Does anybody have any insight, comments -- or suggestions --  at all?  
0
David ToddSenior DBACommented:
Hi,

try
UPDATE STATISTICS tableName
   WITH FULLSCAN

And see if with better statistics you get more consistent query runs.

What start-up switches are you using for SQL? With Enterprise and 4GB ram, I would be using the 3GB switch ...

From your results, it appears that the first run is caching the data. this suggests that you need to look at performance monitor, and see what the cache and memory and disk are doing while you run these queries.

If you want accurate times without the effect of caching, then dbcc dropcleanbuffers prior to each run. Which will mean that your runs take 2 mins instead of improving to 2 secs.

Look at partitioning the data - easier to do with SQL 2005, but thats beside the point. You can still do so manually ...

>>
Within the structure Ive got two tables -- the 'maintable' holds all latency messages for orderno's across the varying msgSources (exchange logs, gateway logs, pricefeed logs, etc.)  It houses an average of 180 or so million records at a time, 7 days of data.  
<<

That is, put each day in its own table, and create a view with the original table name which is basically
select *
from table1
union all
select *
from table2
... etc

You will need the appropriate index here. The idea being that if a query is for a single day, SQL will be selecting from only that days table, and ignoring everything else.

Tell me more about your server. Are you using file groups? Does each filegroup have their own raid arrray? Does the log file have its own raid array? tempdb on its own array? The OS, paging file and anything else on its own array?

How full are the drives? A drive at 80% full takes 4 times longer to respond than a drive at 20% full. After 80% of capacity, disk performance (for NTFS) deteriorates rapidly.

Are the data files defraged?

Your results above speak to a caching issue. What happened to the server overnight? Did it reboot or anything? I would investigate the options for more ram and the addressing windowing boot options.

Can you do the first performance audit I mentioned about and return the results - available memory, disk queue length, % disk time, % cache hits, etc.

Cheers
  David

PS The reason for the delay in my response is that I'm on the other side of the planet - New Zealand to be precise ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
Hi,

Suggestion: Acquire a SQL 2005 licence, and try using the Database Engine Tuning Adviser - the new index tuning wizard. This can connect to SQL 2000 as well ...

Cheers
  David
0
dbaSQLAuthor Commented:
oh david, very good.  lots of stuff for me to review.  thank you so much
give me an opp to get home, get some things done, and i'll get back online this evening
until then, i've got a few answers for you:
nothing crazy on the startup switches, i am not using the /3g.  please explain why you feel i should

update stats tablename with fullscan  -- just once?  or, maybe once daily?  i already just do the update stats w/out fullscan, every day

i attempted the horizontal partition initially on this project, but it's not happening now for many reasons, and v2005 is too far down the road to even factor in right now.

no fragmentation whatsoever, to my knowledge.  the drives have ample free space.  it is a dedicated resource only for this purpose.

i use a filegroup for data & one for log, in addiiton to the default primary we get from sql.  (but i do change the primary to my bigger data filegroup)

>>Your results above speak to a caching issue. What happened to the server overnight? Did it reboot or anything? I would investigate the options for more ram and the addressing windowing boot options.

no reboots except one scheduled saturday bounce
at night, i drop the indices, load a TON of data, rebuild the inidices and perform maint
so yes, there is quite a bit going on at night

dropcleanbuffers prior to each run -- can you elaborate, please?  

i have to run now, but like i said, i'll be back later this evening
0
dbaSQLAuthor Commented:
My apologies, David. I am back, but only briefly as I am not feeling very well at all.  I must just get some rest now, I will be back tomorrow morning without question.  If you could provide a little feedback re my last post, that would be great.  
0
David ToddSenior DBACommented:
Hi,

Sorry, I thought I had responded.

Okay
DropcleanBuffers is a testing technique to remove the effects of the cache and get repeatable run times.

I'd investigate the 3GB switch because it will allow SQL to use more ram. It takes the 4GB and allows SQL to use 3GB and keeps the server to 1GB. Its part of the initial 32bit design of Windows NT, that there is a 4GB limit, and 2GB for the applications, and 2GB for the OS. If only SQL is running, then the OS doesn't need much, and SQL Enterprise can use more than 2GB ram. Was there any reason you are running Enterprise rather than Standard Edition? Is the OS Enterprise Edition as well? Can you test the benefit of more ram?

Try the update stats fullscan once a day, after rebuilding the indexes. Not sure that it will do much.

Please try the first hardware audit from the link I gave you above. It looks at the server hardware, which is bascially CPU, Ram, Disk. I'm thinking that some of the issue might be that not enough is being cached for your application.

HTH
  David
0
dbaSQLAuthor Commented:
Good lord, that performance audit is pretty robust!!!  But... I am certain it will open my eyes to some things I may not be aware of.  These audits we outta be doing on a regular basis anyway...

but... also....... HOLY SH**.  look at these runtimes:


exec procedurename @timestampdate='2/5/08',@msgsource='A' --:25
exec procedurename @msgsource='A' --:01
exec procedurename @latencytype = 'oa' --:00
exec procedurename @timestampdate='2/05/08',@latencytype = 'oa' --:00
exec procedurename @timestampdate=null,@latencytype = 'oa' --:00
exec procedurename @timestampdate='2/6/08',@latencytype = 'oa',@client='dowbigs'--:02
exec procedurename @timestampdate='2/4/08' -- :00
exec procedurename @timestampdate='2/5/08',@latencytype = 'oa',@client='dev1'--:00
exec procedurename @timestampdate='2/6/08',@latencytype = 'oa',@client='dev1',@msgsource='a'
-- :00
exec procedurename @latencytype='OA', @topcount=100, @msgsource='D' -- :00
exec procedurename @endpoint='aaaa',@timestampdate='2/5/08'--:08

only three of them have a clock at all -- and :25 is VERY tolerable, as far as I am concerned.  if they can't handle that on the front end, w/this much data, then i'm thinking it's their problem, not mine.

i ONLY ran the fullscan.  manually, via qa.  it ran for 1:00:37 on 255,480,943 records.  now my stuff is flying!!!!!!!

while i desperately want to close my eyes, walk away and say it's done... i'm sure it isn't.  
i am going to add the fullscan into my nightly routine.  expecting the runtimes to go through the roof.

and i am hugely inclined to get the partitioned structure in place... but that's just not going to happen soon enough.  so, it's a non-issue.

what are your thoughts, david?



(cross your fingers, cross your fingers, god i don't want to slip on this one again)
0
David ToddSenior DBACommented:
Hi,

The short answer here is that the sampling isn't holding water for you compared to the full-scan. Mostly the sample is okay. This is the second instance I've seen the benefit of a full-scan.

I have a job that runs overnight, and somehow the statistics don't keep up with the inserts, and so I scheduled an update stats on the table, and performance is back. The intranet page has some cute scrollee thing happening on one of the graphics, and after the update stats the data is there before the scrolling finishes.

I think, based on what we've discussed, that updating the stats overnight with a full-scan is the way to go.

You could see if the create index syntax has a switch that specifies the sample for the associated statistic, and maybe a smaller sample might achive the same performance but with a shorter run time when creating the index/updating the statistics.

Cheers
  David
0
dbaSQLAuthor Commented:
Yes, that's it.   I was updating stats, but I was simply not getting them all -- I was just doing an insufficient proportion of them.  

I've already changed tonight's run to do the FULLSCAN.
(cross your fingers)

David, do you mind if I leave this open till tomorrow?
0
David ToddSenior DBACommented:
Hi,

>> ... do you mind if I leave this open till tomorrow?

Go for it!

Cheers
  David
0
dbaSQLAuthor Commented:
Very good.  Thank you, sir.
0
dbaSQLAuthor Commented:
still behaving beautifully, david.  i think we can close this.
thank you very much for your input. i really do appreciate it
0
dbaSQLAuthor Commented:
Excellent!  Thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.