| Author |
Topic |
|
vedjha
Posting Yak Master
228 Posts |
 Posted - 2009-12-07 : 11:12:42
|
| I have wrote a procedure , which increment tempdbdatabase size and allocate memory, I have to refresh it. please guide me how to do by sql query...Ved |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-07 : 13:42:29
|
| my database is in online.I can't always stop database server.I have Refresh tempdb database when website is in process...is it possible??provide me query... :)Ved |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-08 : 20:26:18
|
| this procedure is running online. website does transaction any time . If i restart SQL server, then Transaction will stop. when procedure runs on time , it allocates approx 6 mb size of tempdb database , when it second time then approx 6 mb size of temp database.Approx on day, approx 700-800 transacton commits. As Transaction increases , websites become very slow.Procedure having temptable, which always store data in Temptable and temp data always store in tempdb database. procedure uses tempdb database as memory.Then is it possible to as procedure run then it will automatically Refresh memory and reset temp db database size.Ved |
|
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-08 : 20:31:48
|
| procedure:========================CREATE PROCEDURE [prcLeftProcess]( @ID varchar(20), @FromDate DATETIME, @EndDate DATETIME)ASSET NOCOUNT ONIF NOT EXISTS (SELECT * FROM Members WHERE vaID = @ID) BEGIN PRINT 'Please check your ID' RETURN -100 ENDCREATE TABLE #Temp ( vaID varchar(20) NOT NULL, xID varchar(20) NOT NULL, dtDOJ DATETIME NOT NULL, Anchor INT NOT NULL )CREATE CLUSTERED INDEX IX_Temp ON #Temp (Anchor, xID, vaID, dtDOJ)INSERT #Temp ( vaID, xID, dtDOJ, Anchor )SELECT vaID, vspID, dtDOJ, CASE WHEN 'L' IN (cPlacement, vUnderPlacement) AND vspID = @ID THEN 1 ELSE 0 ENDFROM MembersWHERE vspID IS NOT NULLUNION ALLSELECT vaID, vUnderSpon, dtDOJ, CASE WHEN 'L' IN (cPlacement, vUnderPlacement) AND vUnderSpon = @ID THEN 1 ELSE 0 ENDFROM Members WHERE vUnderSpon IS NOT NULLDECLARE @Anchor INTSET @Anchor = 0WHILE @@ROWCOUNT > 0 BEGIN SET @Anchor = @Anchor + 1 UPDATE t SET t.Anchor = @Anchor + 1 FROM #Temp AS t INNER JOIN #Temp AS x ON x.vaID = t.xID AND x.Anchor = @Anchor WHERE t.Anchor = 0 ENDSELECT vaIDFROM #TempWHERE dtDOJ BETWEEN @FromDate AND @EndDate AND Anchor > 0GROUP BY vaIDORDER BY vaIDGOVed |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-08 : 22:23:11
|
| yes, will we optimize this procedureVed |
|
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-08 : 23:43:54
|
| please helpVed |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-08 : 23:50:38
|
| I'd start with indexes. Is vUnderSpon indexed in Members table? How about vspID?Your clustered index on the temp table shouldn't be created until after the insert. I would also change the index definition to be vaID, Anchor. I would also add a nonclustered index to xID, and then another one to dtDOJ, Anchor. I would not have any indexes that start with Anchor since you are updating that, which means you are going to be shifting data around.With all of these changes, I would recommend verifying the execution plan to see if these index changes are correct or not. You may need to play with them a little more.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-09 : 12:30:06
|
| vunderspon and vspid both are from members tableVed |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-09 : 12:52:02
|
| now, what i Have to do???this procedure give me correct output in a micro second. but it allocate memory in tempdb database.by which Website becomes very slow.is there any option to make performance the website.Ved |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-09 : 13:13:30
|
| no , I am saying that as i use procedure one time , then there is no problem.When no of users increment then website becomes very slow by this procedure.I have approax 5 lakh data in members table.is there any option to optimize this procedureVed |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-09 : 13:24:28
|
| Your clustered index on the temp table shouldn't be created until after the insert. I would also change the index definition to be vaID, Anchor. I would also add a nonclustered index to xID, and then another one to dtDOJ, Anchor.I would not have any indexes that start with Anchor since you are updating that, which means you are going to be shifting data around.With all of these changes, I would recommend verifying the execution plan to see if these index changes are correct or not. You may need to play with them a little more.may u make understand this, another time, please... :)Ved |
|
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-09 : 13:26:12
|
| will u change my code as u say...then it will easily understable to me...Ved |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-12-09 : 13:37:38
|
| i am requesting u, please help me... i am in trouble....Ved |
|
|
|
|
Refresh TempDB Database