Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

My stored procedure is creating 20,000+ tempdb extent locks

Status
Not open for further replies.
gbrian

gbrian

IS-IT--Management
Sep 22, 2004
96
US
Hello...this is a nightmare, please help!

I have a stored procedure that is a combination of 15 separate queries which each return a result in the form of 'description', count(some_field), and I am using UNION ALL to combine all of these. The result is something that might look like this

description count
---------------------
value1 50
value2 100
value3 75

and so on. the reason I am doing it this way is so that I can return a bunch of metrics for use in excel dashboards.

here is an example of the stored procedure:

BEGIN
EXEC ('

/**OVERALL VISITS**/
select count(distinct(cs.sessionID)), ''OVERALL: visits'' as what from ['+@logging+'].dbo.customlogs as cl WITH (NOLOCK) , ['+@logging+'].dbo.customlogssubmissions as cs WITH (NOLOCK) where cl.submissionid = cs.submissionid and cl.customlogtypeid=15 and cs.submissiondatetime between '''+@DateMin+''' and '''+@DateMax+'''

union all

/**OVERALL VISITORS**/
select count(distinct(cs.userID)), ''OVERALL: visitors'' as what from ['+@logging+'].dbo.customlogs as cl WITH (NOLOCK) , ['+@logging+'].dbo.customlogssubmissions as cs WITH (NOLOCK) where cl.submissionid = cs.submissionid and cl.customlogtypeid=15 and cs.submissiondatetime between '''+@DateMin+''' and '''+@DateMax+'''

This goes on with many more queries some of which are pulling data from different databases, as well (JOINs).

The queries have absolutely no queries other than SELECT so I am unsure why there are so many extent locks in the database?

Anyways, these queries have started becoming troublesome as the database grows in size. It used to take 40 minutes but now can run for over 15 hours and return nothing. Other times it stops due to the tempdb filling up the hard drive, intra-query parallelism, or other tempdb related errors.

I'm unsure how to fix this and would appreciate a short-term fix as well as any suggestions as to how I could better produce the results in this format.

Thanks in advance.
 
Sort by date Sort by votes
How big is this database?

I would recommend that you create a temp table, and then do a series of Insert/Exec's. Like this...

Code: 
[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #Temp(RecordCount [COLOR=blue]Int[/color], Description [COLOR=blue]VarChar[/color](100))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #Temp
[COLOR=blue]Exec[/color] ([COLOR=red]'
[/color]		[COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]distinct[/color](cs.sessionID)), 
               [COLOR=red]''[/color]OVERALL: visits[COLOR=red]''[/color] [COLOR=blue]as[/color] what 
        [COLOR=blue]from[/color]   [[COLOR=red]'+@logging+'[/color]].dbo.customlogs [COLOR=blue]as[/color] cl [COLOR=blue]WITH[/color] (NOLOCK) , 
               [[COLOR=red]'+@logging+'[/color]].dbo.customlogssubmissions [COLOR=blue]as[/color] cs [COLOR=blue]WITH[/color] (NOLOCK) 
        [COLOR=blue]where[/color]  cl.submissionid = cs.submissionid 
               and cl.customlogtypeid=15 
               and cs.submissiondatetime between [COLOR=red]'''+@DateMin+'''[/color] and [COLOR=red]'''+@DateMax+'''[/color]
      [COLOR=red]')
[/color]

[COLOR=blue]Insert[/color] 	[COLOR=blue]Into[/color] #Temp
[COLOR=blue]Exec[/color]   	([COLOR=red]'
[/color]		[COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]distinct[/color](cs.userID)), 
			   [COLOR=red]''[/color]OVERALL: visitors[COLOR=red]''[/color] [COLOR=blue]as[/color] what 
        [COLOR=blue]from[/color]   [[COLOR=red]'+@logging+'[/color]].dbo.customlogs [COLOR=blue]as[/color] cl [COLOR=blue]WITH[/color] (NOLOCK) , 
               [[COLOR=red]'+@logging+'[/color]].dbo.customlogssubmissions [COLOR=blue]as[/color] cs [COLOR=blue]WITH[/color] (NOLOCK) 
        [COLOR=blue]where[/color]  cl.submissionid = cs.submissionid 
               and cl.customlogtypeid=15 
               and cs.submissiondatetime between [COLOR=red]'''+@DateMin+'''[/color] and [COLOR=red]'''+@DateMax+'''[/color]
       [COLOR=red]')
[/color]
[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] #Temp

Also, you should make sure that each query is as fast as possible. You may be pleasantly surprised to find that a couple indexes on your tables will improve performance alot.

Here's what I suggest. Open Query Analyzer. Use the @logging database. Copy/paste this query:

Code: 
[COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]distinct[/color](cs.sessionID)), 
       [COLOR=red]'OVERALL: visits'[/color] [COLOR=blue]as[/color] what 
[COLOR=blue]from[/color]   customlogs [COLOR=blue]as[/color] cl [COLOR=blue]WITH[/color] (NOLOCK) , 
       customlogssubmissions [COLOR=blue]as[/color] cs [COLOR=blue]WITH[/color] (NOLOCK) 
[COLOR=blue]where[/color]  cl.submissionid = cs.submissionid 
       and cl.customlogtypeid=15 
       and cs.submissiondatetime between [COLOR=red]'XXXX'[/color] and [COLOR=red]'YYYY'[/color]

Make sure you put a valid date range where I have XXXX and YYYY. Press CTRL-K and then run the query. At the bottom of the query window, you will see an execution plan. Check this execution plan for Table Scans. Table scans are horrible for performance. Once you determine what column the table scan in happening on, you should add an index to the table for that column. This will boost performance a lot.

And finally, you should start using ANSI joins.

That last query I show, would look like this with ANSI joins:

Code: 
[COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color]([COLOR=#FF00FF]distinct[/color](cs.sessionID)), 
       [COLOR=red]'OVERALL: visits'[/color] [COLOR=blue]as[/color] what 
[COLOR=blue]from[/color]   customlogs [COLOR=blue]as[/color] cl [COLOR=blue]WITH[/color] (NOLOCK) 
       [b][COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] customlogssubmissions [COLOR=blue]as[/color] cs [COLOR=blue]WITH[/color] (NOLOCK) 
          [COLOR=blue]On[/color] cl.submissionid = cs.submissionid [/b]
[COLOR=blue]where[/color]  cl.customlogtypeid=15 
       and cs.submissiondatetime between [COLOR=red]'XXXX'[/color] and [COLOR=red]'YYYY'[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
George,

Thanks so much - I will try these things you have suggested.

As far as indexing goes, the majority of the queries are searching a field [querystring] for a string. This field can be 100s of characters long and is searched using wildcards or specific substrings.

This table has millions of records and I can imagine indexing this field would not help. The other fields that are used in this table are [sessionid] and [date created].

Do you have any advice as to indexing this table properly?

Thanks again for your advice - I'll let you know if I get anywhere!

Brian
 
Upvote 0 Downvote
String fields are difficult. If you are always looking for data that is at the beginning of the field, then an index will improve performance.

You might want to read up on 'Full Text Indexing'. This is a feature that i have never used, but it might be appropriate in your case.

I wish you luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
George,

To show you a troublesome query that uses this non-indexed table:

Code: 
select count(distinct(userid)), 'ZOOM: get specs' from [mazda_at_logging_stats].dbo.sessionlogs WITH (NOLOCK)
where sessionid in 
     (select sessionid from [mazda_at_logging_stats].dbo.pagelogs WITH (NOLOCK) 
     where querystring like '%2fBuyingAMazda[%]2fWhatNext[%]2fgetSpecs.htm[%]3fproduct%' 
     and datecreated between '01/31/2007' and '02/02/2007') 
and (sessionid in 
     (select sessionid from [mazda_at_logging_stats].dbo.pagelogs WITH (NOLOCK)   
     where querystring like '%EDAT_%'
     and datecreated between '01/31/2007' and '02/02/2007') 
or sessionid in 
     (select sessionid from [mazda_at_logging_stats].dbo.pagelogs WITH (NOLOCK)  
     where referer like '%everydrivesathriller.com%'
     and datecreated between '01/31/2007' and '02/02/2007'))

Thanks again.
 
Upvote 0 Downvote
Run this and post the results back here.

Code: 
[COLOR=blue]Use[/color] mazda_at_logging_stats
[COLOR=blue]go[/color]
sp_helpindex [COLOR=red]'pagelogs'[/color]
sp_helpindex [COLOR=red]'sessionlogs'[/color]



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
IDX_PAGELOGSID nonclustered, unique located on PRIMARY PageLogsID
PK_PageLogs clustered, unique, primary key located on PRIMARY PageLogsID


IDX_SESSIONID nonclustered, unique located on PRIMARY SessionID
PK_SessionLogs clustered, unique, primary key located on PRIMARY SessionID
 
Upvote 0 Downvote
Aha...

Each table ONLY has 2 indexes. The odd thing is that each index is the same. One is clustered & primary key, and the other is non-clustered. So... you have 2 indexes that are doing the same thing. The clustered primary key is good. Get rid of the other ones.

Get rid of:
IDX_PAGELOGSID
IDX_SESSIONID

They are not helping you at all.

In the pagelogs table, create an index on SessionId and DateCreated.

I suspect the index you want is...

Code: 
[COLOR=blue]Drop[/color] [COLOR=blue]Index[/color] PageLogs.idx_pageLogsId

[COLOR=blue]Drop[/color] [COLOR=blue]Index[/color] SessionLogs.idx_SessionId

[COLOR=blue]Create[/color] [COLOR=blue]Index[/color] idx_PageLogs_SessionId_DateCreated
[COLOR=blue]On[/color] PageLogs (SessionId, DateCreated)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Is "IF EXISTS()..." better than "IF (SELECT COUNT...) > 0"
Replies
4
Views
1K
gmmastros
gmmastros
ahmedsa2018
  • Locked
  • Question
the goal mfrom asking question is t
Replies
0
Views
93
ahmedsa2018
ahmedsa2018
peac3
  • Locked
  • Question
Gaps and islands
Replies
8
Views
225
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
How to display the results of my query delimited with pipes?
Replies
9
Views
150
WomanPro
WomanPro
Cpreston
  • Locked
  • Question
Field change as caused issues to store procedure
Replies
19
Views
191
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top