InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:22:25 GMT20http://www.sqlservercentral.com/Forums/Topic1304800-360-1.aspxHi,Before I start, a little information and history. We had business objects on a very fast box (24 processors and 72GB memory) for test, and a shared test SQL server which limped along on 2 processors and about 6GB of memory. As the BOBJ box did very little being a 32 bit piece of software, a request was put to IT to allow SQL to be added to the same box, which was done. The main reason was the time it took to test jobs out on the Test SQL server which was up to 10 times the time for our live server (I know live and test should ideally be the same, but its not what we had).Our live SQL server is only a little better than our old test box and is shared with some processor intensive programs like message vault etc, so our belief was that moving the SQL server to the fast test BOBJ machine would enable testing to be done much faster than live.What we saw was that all the processors maxed out and the whole system would grind to a halt. We were able to do this with four lines of SQL code linking to two tables (which rules out BOBJ), but also the automated jobs that ran on the live server (remember not very fast) would max out and virtually stop on the new Test server. I don't have full access to the machine as my position is one of business intelligence and using SQL code rather than the back end, but our IT department has drawn a blank to the reason that this has happened. They have put in some changes which has basically limited it to 8 processors, which then max out and the job still fails. We run SQL 2005 on windows 5.2 R2. The SQL was installed from new, but the disks were disconnected and reconnected to the new server with a change to Directory from E: to F: (san disks)Processors are 6 quad core and 72GB memory.Please feel free to throw anything into the hat, as I have no idea what is causing this and I am prepared to check as much as I can gain access to or pass other bits to our IT department if I don't have the access.Wed, 23 May 2012 03:32:34 GMTGattacahttp://www.sqlservercentral.com/Forums/Topic1303575-360-1.aspxI am pretty new to resolving query performance issues, just bear with me...Why should i create an index with Include Columns(why can't i just say create a composite index with Include Column as one of the field)What's the actual difference between EqualityUsage and InequalityUsage Column??Mon, 21 May 2012 11:57:33 GMTPradeepVallabhhttp://www.sqlservercentral.com/Forums/Topic1303987-360-1.aspxI have found a couple of indexes which have fragmentation at about 20% every week.These indexes always get into a (if fragmentation <30% then Reorganise) query.As such, this index has not been rebuilt for 1 year, since every week, the fragmentation is sitting at around 20%.Reorganising does not update stats, so it looks like we have a reorganised index with stats that are one year old,according to the stats_date for these indexes.But I am not sure if I should take that value as true since the DB is set to auto update stats, and despite this, the stats date is old when querying the age.Should I leave everything as is, or should I change the job to also include a check against stats age to determine whether to rebuild the index.Or should I rather explicitly update stats in these cases without a rebuild.Are there any other related issues that I am not taking into account?On the other hand, rebuilding all tables with rowcount less than 40 million takes about 2 minutes so in this case, is it rather better to rebuild, considering the SAN is that fast?Tue, 22 May 2012 03:04:15 GMTMadTesterhttp://www.sqlservercentral.com/Forums/Topic1304297-360-1.aspxDear Experts,I have a scernario where i want to retrieve data from 5 tables:OVPM - Doc Num,Doc Type,Doc Total,Journal Remarks,Cash A/CVPM4 - Doc Num,Row Num,Account,Paid,Description,Acc Name, Pay typeOACT - Account Code,Account NameOPCH - Doc Num,Doc type,Doc Total, Journal RemarksPCH1 - Doc Entry,Row NumI don't wish to have duplicate data. attached is the sample data from each table in xlsPlease kindly help me.Thanks,WamalwaTue, 22 May 2012 10:26:23 GMTisaac.wamalwa1983http://www.sqlservercentral.com/Forums/Topic1303238-360-1.aspxHi, I just want to see if anyone else has noticed an interesting effect with perfmon and Profiler?I'm running a perfmon data collector set and Profiler SP_TSQL_Replay set on a production server from a different server (so I don't skew the results and possibly bork the prod server performance). I'm going to collect about 6 hours of data then take it offline and crunch it in Excel, Profiler and PAL.The destination file directories for both tools are on a UNC path i.e. \\MYSERVER\c$\myfolder\file1.blg and file2.trc.The file size of both the .blg and .trc isn't increasing in real-time like I'd expect it to if the files remained local. The .blg is static at 65,536 and the .trc is 0 bytes.Yet Profiler and perfmon are both happily collecting stats and (apparently) recording information.I tested perfmon beforehand by running a trace for 30 minutes then stopping it. Once stopped, the file size of the destination file rose from 65,536 bytes to around 200k, suggesting the content had been dumped in afterwards.So, if the real-time data isn't been written to disk in real-time, where is it going? And do I need to worry about this?Mon, 21 May 2012 03:30:41 GMTderek.colleyhttp://www.sqlservercentral.com/Forums/Topic970566-360-1.aspxHi All,I want to execute SQL Profiler Replay Trace from batch file to a fix server. I am able to call the Trace through command prompt but I am not getting way to run this replay to specific server.If any one ever executed SQL Profiler through batch file will be very helpful for me.Your contribution will be highly appreciated.Tue, 17 Aug 2010 10:11:04 GMTtrivedi.nikeshhttp://www.sqlservercentral.com/Forums/Topic1301227-360-1.aspxI'm doing some deadlock investigations where the locks are page locks. I have the table name involved from the deadlock trace but dbcc page returns metadata : ObjectID = 0 and IndexID = -1, I was wondering if anyone could cast some light on what these values mean?m_type = 1 so its a data page.cheersgeorgeWed, 16 May 2012 11:03:09 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1298757-360-1.aspxSQL Server 2005 standard table1 has 4 millions recs. The below query returns 480,000 recs. There is non-clustered index on table1.Date1.Below is a simple query. This query uses parallelism.SELECT count(*)FROM table1WHERE ( table1.Date1 >= '10/01/2010' AND table2.Date1 <= '04/30/2011')However, when the date range changes to system based dates, the query runs in serial. SELECT count(*)FROM table1WHERE ( table1.Date1 >= CONVERT(DATETIME, '10/01/' + CAST(DATEPART(yyyy, dateadd(yyyy, -2, getdate())) AS CHAR(4))) AND table2.Date1 <= cast(CONVERT(varchar(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(yyyy, -1, getdate())),0)),101) as datetime)))I don't understand why the 2nd query is not using parallelism. How does the constant date vs dynamic system based dates in predicate affect the SQL optimizer to choose Serial vs parallel execution plan? In the execution plan of 2nd query, estimated row count from table1 is 1., but why? Is there a way to still get parallel plan for dynamic dates (without using stored procedures)?Any help is greatly appreciated.Thanks much.Fri, 11 May 2012 10:38:22 GMTprapoornakhttp://www.sqlservercentral.com/Forums/Topic1296669-360-1.aspxHi you all!I have a doubt about setting fill factor. I've read that when we use a identity column on a table and this table has no update, we must use a fill factor with 95 or higher. And so, when we use a date as part of the key in a historical table, should we follow the same idea when using identity column since the rows we are inserting each passing day is always increasing (the snapshot) ???. Just to clarify... it is about a tables used in a data warehouse where we create a snapshot from the entire table all days.Regards,Rafael Melo - BrTue, 08 May 2012 12:09:36 GMTrafael_sihttp://www.sqlservercentral.com/Forums/Topic1294341-360-1.aspxHello all,This is my first post so please tolerate me for all my silly mistakes.I am facing issues while troubleshooting Deadlock issues, we are getting data from some source using web services and putting it up in a Queue. This data is then feed into SQL server database tables using window services created on the server. We primarily used only one service but later increased them to 8 services that run all at the same time and execute same piece of code. Here, in the code we are trying to insert data in to 16 different tables. That means all 8 services try to insert data in same 16 tables all at the same time. Now, during this we are facing deadlocks. We have run SQL profiler with template: TSQL_Locks and found that there are deadlocks while insertions. I am pasting the deadlock details here:Deadlock graph <deadlock-list> <deadlock victim="process559ae08"> <process-list> <process id="process559ae08" taskpriority="0" logused="0" waitresource="OBJECT: 8:740197687:9 " waittime="11030" ownerId="1864028770" transactionname="user_transaction" lasttranstarted="2012-04-24T07:31:00.043" XDES="0x159dee3b0" lockMode="X" schedulerid="14" kpid="19016" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-24T07:31:00.100" lastbatchcompleted="2012-04-24T07:31:00.043" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="4828" loginname="sqluser" isolationlevel="read committed (2)" xactid="1864028770" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="760" sqlhandle="0x02000000758b67241f8bad4a6c289d3a8cbdb950255d73cb">INSERT INTO [ABC]([BatchRecordID],[DateOfBirth],[Disability],[Email],[LEACode],[SITSLastName],[Country_Birth],[FeeStatus],[LEAName],[Nationality],[BeenInCareValue],[ParentsHigherEducationCode],[BeenInCareCode],[ParentsHigherEducationValue],[UCASID],[Gender],[HomeEmail],[SITSStudentNumber],[Title],[SITSFirstName],[StudentNo],[Country]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22) </frame> <frame procname="adhoc" line="1" stmtend="1176" sqlhandle="0x0200000058ff67308d69296547143dae303a7e80b9dd8617">INSERT INTO ABC (BatchRecordID , DateOfBirth, Disability, Email, LEACode, SITSLastName, Country_Birth, FeeStatus, LEAName, Nationality, BeenInCareValue, ParentsHigherEducationCode, BeenInCareCode, ParentsHigherEducationValue, UCASID, Gender, HomeEmail, SITSStudentNumber, Title, SITSFirstName, StudentNo, Country) VALUES(--------------------------Values cleared out----------) </frame> </executionStack> <inputbuf>INSERT INTO ABC(BatchRecordID , DateOfBirth, Disability, Email, LEACode, SITSLastName, Country_Birth, FeeStatus, LEAName, Nationality, BeenInCareValue, ParentsHigherEducationCode, BeenInCareCode, ParentsHigherEducationValue, UCASID, Gender, HomeEmail, SITSStudentNumber, Title, SITSFirstName, StudentNo, Country) VALUES(--------------------------Values cleared out----------)INSERT INTO DEF(BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, APFSeqNo, Decision, SeqNum) VALUES(--------------------------Values cleared out----------)INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision) VALUES(--------------------------Values cleared out----------) </inputbuf> </process> <process id="process4875048" taskpriority="0" logused="0" waitresource="OBJECT: 8:740197687:0 " waittime="11029" ownerId="1864028790" transactionname="user_transaction" lasttranstarted="2012-04-24T07:31:00.060" XDES="0x8cca09950" lockMode="X" schedulerid="10" kpid="9492" status="suspended" spid="125" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-24T07:31:00.120" lastbatchcompleted="2012-04-24T07:31:00.060" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="8272" loginname="sqluser" isolationlevel="read committed (2)" xactid="1864028790" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="580" sqlhandle="0x020000005b791e049c815fb4512ce3b249070e0b3f6635df">INSERT INTO [ABC]([BatchRecordID],[UCASID],[UKEntryDate],[FeeStatus],[SITSStudentNumber],[Title],[SITSLastName],[Email],[SITSFirstName],[Country_Birth],[Nationality],[Country],[Gender],[HomeEmail],[StudentNo],[Disability],[DateOfBirth]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17) </frame> <frame procname="adhoc" line="1" stmtend="898" sqlhandle="0x02000000d7b55c16df84264b7965df66ab75e47cda870abb">INSERT INTO ABC(BatchRecordID , UCASID, UKEntryDate, FeeStatus, SITSStudentNumber, Title, SITSLastName, Email, SITSFirstName, Country_Birth, Nationality, Country, Gender, HomeEmail, StudentNo, Disability, DateOfBirth) VALUES(--------------------------Values cleared out----------) </frame> </executionStack> <inputbuf>INSERT INTO ABC(BatchRecordID , UCASID, UKEntryDate, FeeStatus, SITSStudentNumber, Title, SITSLastName, Email, SITSFirstName, Country_Birth, Nationality, Country, Gender, HomeEmail, StudentNo, Disability, DateOfBirth) VALUES(--------------------------Values cleared out----------)INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision) VALUES(--------------------------Values cleared out---------)INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Response, Decision, OfferSummary) VALUES(--------------------------Values cleared out---------)INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision </inputbuf> </process> </process-list> <resource-list> <objectlock lockPartition="9" objid="740197687" subresource="FULL" dbid="8" objectname="A.dbo.ABC" id="lock246aa1f80" mode="IX" associatedObjectId="740197687"> <owner-list> <owner id="process4875048" mode="IX"/> </owner-list> <waiter-list> <waiter id="process559ae08" mode="X" requestType="wait"/> </waiter-list> </objectlock> <objectlock lockPartition="0" objid="740197687" subresource="FULL" dbid="8" objectname="A.dbo.ABC" id="locke6668a300" mode="X" associatedObjectId="740197687"> <owner-list> <owner id="process559ae08" mode="X"/> </owner-list> <waiter-list> <waiter id="process4875048" mode="X" requestType="wait"/> </waiter-list> </objectlock> </resource-list> </deadlock></deadlock-list> The deadlock occurs on table ABC. This table has a primary key on ApplicantID and thus has a clustered index on this, Applicantid is the identity column. We have a non clustered index on BatchRecordID that is non unique. This is created as after insert we need to perform some update and select statements.After insertions, we have a few(2-3) update statements on tables ABC and another table GHI. We then will perform select on these 16 tables and move data to our final tables i.e. will perform insert again. All this need to be either get successful and reach to final tables or get rolled back.We have tried to drop non clustered index here that resulted in successful insertions but the deadlocks started occurring at the next level i.e. at update in ABC table as there is now only clustered index(we deleted the non clustered index as mentioned earlier).We cannot reduce isolation level also. We may not be able to use non overlapping as we are using 8 processes to run the same code.We are doubtful to DISABLE LOCK ESCALATION here as we fear from memory issue on the server. Is memory a concern if we are disabling lock escalation?Please suggest how to avoid deadlocks and maintain concurrency. We may want to increase concurrency to 16 services/streams if we are able to resolve deadlock situation but at this point we would like to restrict ourselves to 8 services only.Thanks a lot.Wed, 02 May 2012 22:48:29 GMTdelhi_eyehttp://www.sqlservercentral.com/Forums/Topic1295679-360-1.aspxHi,In which case , we can use DBCC CHECKDB in production/testing/dev environment.Sun, 06 May 2012 09:05:26 GMTbandarimohan 57273http://www.sqlservercentral.com/Forums/Topic1292334-360-1.aspxHi,Is there any advantage in creating separate database user for each application (Windows Service, Windows Desktop) that connects to the database? We trying to figure out if this can help as we are on the process of overhauling our services performance and notice that one of the service is having way too many processing compared to the other so we are thinking of creating separating user for each if that helps.If we have exclusive database user for each of our application, would it be possible to monitor which database user having higher usage?Please let me know your thoughts. Thanks in advance.Mon, 30 Apr 2012 03:12:50 GMTbion81http://www.sqlservercentral.com/Forums/Topic1293832-360-1.aspxhi guys,I am working on a bit of a hybrid system - not OLTP and non OLAP. It is basically a staging database that converts Unix flat files into a dataset for inserting into another SQL database.I have a table with 110M rows and about 40 columns, mainly decimal(10,2). 4 times a week we read in 3.8M rows for a new update_date and clear out 3.7M rows for the update_date that was 45 days ago (dataset is growing by 60K rows per month)The table currently has three non-clustered indexes;IDX1: Acc_noIDX2: update_dateIDX3: Acc_no/Update (Unique)I am running a query (I won't bore you with the details) that basically compares the records for one update_date with the records for the previous update_date and writes into another table the records which are different (doing a not equality compare on the other 38 fields) or did not exist in the previous day's data. The idea is that this other table permanently holds records which are significant (i.e. different from the previous run)for some reason I am having to force the optimiser to use the IDX3 using the WITH (INDEX(IDX3)) syntax which is fine, but because I am looking at fields not in the index the query plan is showing RID lookups to get the non indexed values. I could create covering fields in the index, but as it is every field I am comparing, the index would be the same size as the table. If I were to convert IDX3 into a clustered index, would this remove the RID lookup.I understand there there would be a one-off perfomance hit on the database as the data will be rewritten on the disk and the other two non-clustered indexes will be rebuilt. As the delete will be removing entire pages from the index and the insert will be adding new pages, should I expect a performance hit on the CRUD operations? The data is imported one date at a time, but the account numbers are not in sequence, will this cause page split issues if the data is imported using an SSIS dataflow tasktimings examples for current setup.[code="sql"]SELECT C.update_date, C.acc_no, P.Update_date FROM myTable C WITH(INDEX(IDX3)) JOIN myTable P WITH (INDEX(IDX3)) on C.acc_no = P.Acc_no AND P.update_date = '20120427' WHERE C.update_date = '20120430' and P.update_date is null [/code]find all records in current date that do not exist in previous date: 2m36s to find 6650 records from 3.8M records. - Using only the index seek[code="sql"]SELECT C.update_date, C.acc_no, C.Shop_code, P.Update_date FROM myTable C WITH(INDEX(IDX3)) JOIN myTable P WITH (INDEX(IDX3)) on C.acc_no = P.Acc_no AND P.update_date = '20120427' WHERE C.update_date = '20120430' and P.update_date is null [/code]find all records (udpate_date,Acc_no, Shop_code) in current date that do not exist in previous date: returned no records after 12 minutes - using index seek and RID lookup because shop_code is not in the indexMy question is: If I convert IDX3 to a clustered index, will I eliminate the RID lookups and therefore be able to access the other fields for the comparisons Wed, 02 May 2012 07:34:43 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1292026-360-1.aspxI have heard rumours from my peers that defragging the MDF will in some way logically fragment the indexes so that it would necessitate another index rebuild.In my head, the two operations work on 2 different layers so they should be mutually exclusive and hence I disagree with these rumours.As such, I am guessing that an MDF defrag after an index rebuild, or a rebuild after an MDF defrag will result in an index which is in the same logical and physical state and not need further manipulation.But a DBA I am not and hope you pro's could help me gain clarity on this issue.Sat, 28 Apr 2012 07:15:03 GMTMadTesterhttp://www.sqlservercentral.com/Forums/Topic1293036-360-1.aspxGuys,I have a table with 48M rows in it which I had to copy from one database to another (same instance). to improve performance of the copy I disabled the indexes on the table (non clustered, one Unique, one not)I have run the Alter Index ()... Rebuild .... Script and activity monitor is showing the reindex as being blocked by another process (activity shows as DBCC) which my DBA says is running a fragmentation query.This seems to be seriously degrading the performance of MSSM (2005); The original copy appears to have stopped responding (even though it doesn't say it in the title bar) - the query timer has stopped and I cannot open any tree nodes in the database explorer window.I have also started a new instance of MSSM and am getting query timeouts when trying to open the Tables node of the Database Explorer treeI can't find any details on-line about a rebuild causing a fragmentation query. Would I do better kill the fragmentation and rebuild processes and then drop and re-create the index rather than try to rebuild it?Tue, 01 May 2012 05:24:41 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1286196-360-1.aspxcAN SOMEONE help me optimize this query - I am really struggling. I have attached explain plan and query. If there is anything else needed please let me know as I really need help thanksThu, 19 Apr 2012 00:01:29 GMTdjordan 41144http://www.sqlservercentral.com/Forums/Topic1291456-360-1.aspxCurrently we have a web front end running IIS, and a SQL 2005 back end (SP3), we also have a 3rd server which is a reporting server (SSRS). Periodically we will get lockups/slowdowns where our web front end will basically just be a white screen. When I hit all three servers, CPU and Memory are low, they all seem to not be doing much of anything. I'm also running a trial version of SQL Diagnostics Manager but don't see that there are deadlocks, etc. When I restart SQL however, the issue goes away. I also have noticed that the number of user connections to the database are high when we get our slowdowns, which I think is the start of my "paper trail". For example, at say 11:21am our user connections are arund 65 and the site is running fine. At 11:22am, the site is appearing frozen or at best very sluggish and there are 140 user connections. User connections at 11:44am go back down to 74, and the site is again very responsive.Any suggestions what I could try, to try and pinpoint what is causing these?Fri, 27 Apr 2012 06:25:24 GMTbillohttp://www.sqlservercentral.com/Forums/Topic458077-360-1.aspxjust wondering if I'm missing a dbcc option, I note that if I run dbcc dropcleanbuffers it only clears the user databases and usage by tempdb is not cleared. I tried checkpointing and dbcc updateusage on tempdb but ti doesn't seem to clear down. SQL 2005 sp2 x64 3215Wed, 20 Feb 2008 09:38:38 GMTcolin.Leversuch-Robertshttp://www.sqlservercentral.com/Forums/Topic1285775-360-1.aspxI have an older server that needed some QWed, 18 Apr 2012 10:10:41 GMTtimscroninhttp://www.sqlservercentral.com/Forums/Topic1289326-360-1.aspxI have a stored procedure implemented and calling of this stored proc from web page is taking more than 300ms, But when write the script in the web page and call the url, it is executing in 50msWhat are the steps that are required to reduce the time to execute a stored proc. Why is sp taking more time than the db script in the pageTue, 24 Apr 2012 13:40:35 GMTTripzhttp://www.sqlservercentral.com/Forums/Topic1120484-360-1.aspxHi,We have MOSS 2007 SP2 databases on SQL Server 2005 EE SP3.We are experiencing the CXPACKET wait type as the top wait type. But with zero wait time (most of the times) and some time 300 MS wait time. After CXPACKET wait type, I can see some locks are happening and causing frequent blocking problem and it lasting for up to 10 to 30 mins and users are unable to access the site completely.We are getting complaints from users that the Share Point application is running slow.How can we prove that the slow performance is due to CXPACKET wait type?Or How can we prove that the slow performance is NOT due to CXPACKET wait type?Please adviceThanksMon, 06 Jun 2011 11:53:27 GMTMani-584606http://www.sqlservercentral.com/Forums/Topic1289785-360-1.aspxI have set alerts on two SQL Server 2005 instances I have in production. One of those alerts is sent whenever the Free Pages performance counter falls below the 5000 pages mark.Both servers normally operate with more than 300000 free pages, but sometimes one of these servers momentarily goes well below 2000 or even 1000 free pages and then goes up again. Again, this only happens with one of the servers.As far as I know, this behavior is not degrading the performance of this server, because it looks to me as if whenever the server is going to starve on free pages, it frees up older data pages from the buffers and populates the Free Buffer List. Theoretically speaking, at least.What I don't understand is why the lazywriter isn't maintaining a larger free buffer list and is 'waiting' for SQL Server to get so short on free pages before freeing up more buffers.So my question is actually twofold:1. How can I determine what/who is causing SQL Server to become short on free pages?2. How can I inspect what is [b]actually[/b] going on here?Wed, 25 Apr 2012 06:24:16 GMTivanglimahttp://www.sqlservercentral.com/Forums/Topic1288655-360-1.aspxHi All,What is the port number for Exchange Server to configure database mail?How can I find the port number?Please advise.Thanks and Regards,Ravi.Mon, 23 Apr 2012 23:42:19 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1286089-360-1.aspxI have been struggling with this for some time and have not been adding an index to a table. Curious if anyone thinks I should add an index that could be 4GB in size based on information I can provide. This is for 1 stored procedure to be faster, but when it's slow it can slow other operations down greatly as it pulls data into cache or writes to TempDB. Query must function in manner it does.Situation:Query - used to take 25 minutes to run, then tuned to 11 minutes average run time by putting maxdop to 1 and breaking into table variable for results and then joining to another table (had tested with many other scenarios but this was the least costly and quickest, with best execution plan to date).Beast of a query on volumes that need to have storage added if an index is added. We are due for space upgrade and not on SAN but DAS and need more rack space (side point but important because index space would be costly on this table for storage).sp_spaceused on table is:rows reserved data index_size unused53508975 12676096 KB 7596784 KB 5071960 KB 7352 KBMultiple joins, must sift through millions of records to compare old values to new values. Execution plan has a 90% cost on an index spool (eager spool).The only reason to add the index would be for this one query, and likely slow everything else down considering the number of updates and insert to the table (would need a good padding on the index as well as a massive rebuild/reorg plan). The query is run a few to ten times per day an causes extensive IO_completion and IO_Latch wait as t pulls from disk and writes to disk on a tempDB that is in a bad place. The Index Spool pulls out all 53508975 records in the table. Any ideas on taming a beast with the info I provided or any questions to get more data for beast taming?Wed, 18 Apr 2012 14:52:55 GMTmatt.newmanhttp://www.sqlservercentral.com/Forums/Topic972148-360-1.aspxHello All,I have this situation:For several reports we need to extract about 5 millions records from a table of 124 million ( 173 gb)I am able to use the clustered index ( with a "like" statement) and the only other parameter is a column with a state so it is not helping much, the thing is that the resultant table is really big ( 5 gb ) and is taking forever to load and it is also consuming lots of resources from the server ( we usually get the error there is not enough system memory to run this query... ) That said, I need to find a way to obtain the same data in a faster way or at least not consuming that much resources, maybe in chunks but I am not sure how to do it...We have a server with 16 processors and 42 gb of ram, sql server 2006 sp3Thanks a losThu, 19 Aug 2010 13:50:35 GMTricardo_chicashttp://www.sqlservercentral.com/Forums/Topic1288165-360-1.aspxHi,What is the best pracise in mainaining autgrouth value for user and tempDB Databases.For large Db and as well as small DB's........Thanks In Advance.Mon, 23 Apr 2012 07:45:08 GMTkrishnaprasad14http://www.sqlservercentral.com/Forums/Topic1286378-360-1.aspxWe are currently trying to query a view which takes a considerable amount of time, in order to try and speed this up i am trying to create some indexes on the view however i believe to do this i need a schemabinding I have tried to do this using the following statement however does not seem to work. Any help would be greatly appreciated to speed up the querying of my view.CREATE VIEW GIS_Web_ViewWITH SCHEMABINDINGSELECT CAST(CASE WHEN PAO_START_NUMBER IS NULL THEN NULL ELSE PAO_START_NUMBER END AS VARCHAR(10)) + (CASE WHEN PAO_START_SUFFIX IS NULL THEN '' ELSE '-' END) + (CASE WHEN PAO_START_SUFFIX IS NULL THEN '' ELSE PAO_START_SUFFIX END) + (CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE '-' END) + CAST(CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE PAO_END_NUMBER END AS VARCHAR(10)) AS PAO, tbl_24.PAO_TEXT, tbl_15.STREET_DESCRIPTOR, tbl_15.LOCALITY_NAME, tbl_24.POST_TOWN, tbl_24.POSTCODE, tbl_21.X_COORDINATE, tbl_21.Y_COORDINATE, (CASE WHEN tbl_21.ORGANISATION IS NULL OR ORGANISATION = '' THEN '' ELSE tbl_21.ORGANISATION + ',' END) + (CASE WHEN SAO_START_NUMBER IS NULL THEN '' ELSE (CAST(CASE WHEN SAO_START_NUMBER IS NULL THEN '' ELSE SAO_START_NUMBER END AS VARCHAR(10)) + (CASE WHEN SAO_START_SUFFIX IS NULL THEN '' ELSE '-' END) + (CASE WHEN SAO_START_SUFFIX IS NULL THEN '' ELSE SAO_START_SUFFIX END) + (CASE WHEN SAO_END_NUMBER IS NULL THEN '' ELSE '-' END) + CAST(CASE WHEN SAO_END_NUMBER IS NULL THEN '' ELSE SAO_END_NUMBER END AS VARCHAR(10)) + ',') END) + (CASE WHEN SAO_TEXT IS NULL OR SAO_TEXT = '' THEN '' ELSE SAO_TEXT + ',' END) + (CASE WHEN PAO_START_NUMBER IS NULL THEN '' ELSE (CAST(CASE WHEN PAO_START_NUMBER IS NULL THEN '' ELSE PAO_START_NUMBER END AS VARCHAR(10)) + (CASE WHEN PAO_START_SUFFIX IS NULL THEN '' ELSE '-' END) + (CASE WHEN PAO_START_SUFFIX IS NULL THEN '' ELSE PAO_START_SUFFIX END) + (CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE '-' END) + CAST(CASE WHEN PAO_END_NUMBER IS NULL THEN '' ELSE PAO_END_NUMBER END AS VARCHAR(10)) + ',') END) + (CASE WHEN PAO_TEXT IS NULL OR PAO_TEXT = '' THEN '' ELSE PAO_TEXT + ',' END) + (CASE WHEN STREET_DESCRIPTOR IS NULL OR STREET_DESCRIPTOR = '' THEN '' ELSE STREET_DESCRIPTOR + ',' END) + (CASE WHEN LOCALITY_NAME IS NULL OR LOCALITY_NAME = '' THEN '' ELSE LOCALITY_NAME + ',' END) + (CASE WHEN POST_TOWN IS NULL OR POST_TOWN = '' THEN '' ELSE POST_TOWN + ',' END) + (CASE WHEN POSTCODE IS NULL OR POSTCODE = '' THEN '' ELSE POSTCODE + ',' END) AS RESULTSFROM dbo.LAND_AND_PROPERTY_IDENTIFIER_RECORD24 AS tbl_24 LEFT OUTER JOIN dbo.STREET_DESCRIPTOR_RECORD_TYPE15 AS tbl_15 ON tbl_24.USRN = tbl_15.USRN LEFT OUTER JOIN dbo.BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21 AS tbl_21 ON tbl_24.UPRN = tbl_21.UPRNGOThu, 19 Apr 2012 07:00:10 GMTsmithdhttp://www.sqlservercentral.com/Forums/Topic1285531-360-1.aspxDear All,Is it require to update statistics after rebuilding index.As per my knowledge after rebuliding index we no need to run update statistics.But for re-org it's required.If we run after rebuild it will update the other statistics.If i run sp_updatestas o a database there will be no issue.If i am wrong kinldy guide me with clear idea.I have read in so many articles but i am having little confusion.Kindly suggest me on the same.Wed, 18 Apr 2012 04:36:54 GMTMinto Minto(quendans)http://www.sqlservercentral.com/Forums/Topic1285454-360-1.aspxThe select query takes about a min to retrieve 7200 records.Need it to be quick say around 3 seconds[b]The table definition is as follows[/b]create TABLE TMPPACKAGEOPTION ( ID INT IDENTITY(1,1) PRIMARY KEY, SPID int, PACKAGEID INT, PACKAGEDEPARTUREID INT, PACKAGEELEMENTID INT, PACKAGEOPTIONID INT, SERVICETYPEOPTIONNAME VARCHAR(100), SERVICETYPETYPENAME VARCHAR(50), OCCUPANCYTYPENAME VARCHAR(50), SERVICEID INT, SERVICENAME VARCHAR(500), SERVICETYPENAME VARCHAR(50), SERVICEDESCRIPTION VARCHAR(2048), SERVICESTARRATING VARCHAR(50), ELEMENT_NAME VARCHAR(50), CHILDCAPACITY INT, REGIONSHORTNAME VARCHAR(50), REGIONNAME VARCHAR(50), REGIONID INT, PACKAGESERVICEID INT ,CHECKINTIME DATETIME, CHECKOUTTIME DATETIME ,FIXEDDEPARTURE BIT DEFAULT 0 )[b]The problem select statement is as follows[/b] SELECT id, PACKAGEID , PACKAGEDEPARTUREID , PACKAGEELEMENTID , PACKAGEOPTIONID , SERVICETYPEOPTIONNAME , SERVICETYPETYPENAME , OCCUPANCYTYPENAME , SERVICEID , SERVICENAME , SERVICETYPENAME , SERVICEDESCRIPTION , SERVICESTARRATING , ELEMENT_NAME , CHILDCAPACITY , REGIONSHORTNAME , REGIONNAME , REGIONID , PACKAGESERVICEID ,CHECKINTIME ,CHECKOUTTIME FROM TMPPACKAGEOPTION where packageid=891[b]using the database tuning advisor i used the following recommendations[/b]CREATE NONCLUSTERED INDEX [_dta_index_TMPPACKAGEOPTION_Opti] ON [dbo].[TMPPACKAGEOPTION] ( [PACKAGEID] ASC)INCLUDE ( [ID],[PACKAGEDEPARTUREID],[PACKAGEELEMENTID],[PACKAGEOPTIONID],[SERVICETYPEOPTIONNAME],[SERVICETYPETYPENAME],[OCCUPANCYTYPENAME],[SERVICEID],[SERVICENAME],[SERVICETYPENAME],[SERVICEDESCRIPTION],[SERVICESTARRATING],[ELEMENT_NAME],[CHILDCAPACITY],[REGIONSHORTNAME],[REGIONNAME],[REGIONID],[PACKAGESERVICEID],[CHECKINTIME],[CHECKOUTTIME]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]But this did not help as actually i require all the records in the tableHave attached a screenshot of the actual execution plan...Any help would be appreciated... As of now am using a top 300 and using a loop so that i avoid a timeout from the application.Have also attached the actual execution plan with the.sqlplan extensionWed, 18 Apr 2012 01:29:36 GMTvernalrdhttp://www.sqlservercentral.com/Forums/Topic823996-360-1.aspxWhen you have to rebuild an index that has partitions do you have to specify the partitions within the indexes or will simply identifying the index like I have written below do the trick?USE [TestDB]GOALTER INDEX [in_tbl_doc2_address_address_id] ON [TestDB].[tbl_doc2_address] REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )Thanks,ShawnTue, 24 Nov 2009 09:54:36 GMTShawn Pierrehttp://www.sqlservercentral.com/Forums/Topic1279018-360-1.aspxHello,I created a temporary table in a SP :create table #dirtyDocInterventions (ptInterventionId uniqueidentifier, conceptId uniqueidentifier, dataFocusId uniqueidentifier, chartTime dateTime, storeTime dateTime, ptDocumentId uniqueidentifier, isDemographic bit)CREATE NONCLUSTERED INDEX Tind on #dirtyDocInterventions (ptInterventionId)I checked the execution plan (see the following picture) and it losted a lot of time on "Sorting Input" (cost 94 %) by making a order by on ptIntervention (ASC order).The part of the code having issue is :INSERT INTO #dirtyDocInterventions SELECT p.ptInterventionId ,p.conceptId ,p.dataFocusId ,p.chartTime ,p.storeTime ,d.ptDocumentId ,p.isDemographicFROM @MyPtIntervention p INNER JOIN @ptNormData d ON p.ptInterventionId = d.ptInterventionIdI don't understand because I don't specify a Order By. Could you explain me and could you give me a solution to avoid this lost time ?Thanks for your help,EricThu, 05 Apr 2012 10:53:55 GMTERIC CRUDELIhttp://www.sqlservercentral.com/Forums/Topic1281730-360-1.aspxGreetings, I am a SQL database architect / developer on a rather large web-based application with a SQL Server 2005 database as the backend. The application is OLTP with Reporting included in some areas. I have very limited permissions on the SQL Server environments, with the exception of the development environment. We are having performance trouble in the production environment that began being reported after a 'release', which for us means adding and making changes to db objects - stored procedures, tables, views, etc. What is being reported is timeouts in the web interface when performing operations such as saving changes to data and creating records. As part of my troubleshooting, I have been checking ETL processes and find that several of them have been running longer, sometimes a LOT longer, beginning over a month before the release. Our DBA has been somewhat helpful, but has been unable to provide us a focused, honest troubleshooting effort, IMO (I understand he has bandwidth issues of his own). So I am asking for help in understanding what I am seeing and how to go about intelligently requesting consideration by the DBA. What I can see:I can't run any of the DVMs, but from Activity Monitor, I can see there is locking and blocking. The Wait Types reported are almost all Latch_EX and CXPacket. Usually there is one process that blocks another, which then blocks several others. In one example from an ETL process, the 'main' or 'base' blocking ProcessID is showing 9 times with the 1st showing an hour glass, then the next 8 showing a green circle with a check mark. On the row with the hour glass, the wait type is listed as CXPacket (none of the other rows show a Wait Type) and I can dbl-click the row to get the SQL code. In this case, it is a simple update - changing 'N' to 'Y' for 122K+ records. Code:UPDATE WorkstationSET Workstation.TrackingInfo = 'Y'FROM Orders WITH (NOLOCK) INNER JOIN Workstation WITH (NOLOCK) ON Orders.WorkstationOID = Workstation.WorkstationOID INNER JOIN Carrier_Tracking WITH (NOLOCK) ON Orders.POnumber = Carrier_Tracking.POnumberWHERE Workstation.TrackingInfo = 'N'A month and a half ago, this task ran in 17 seconds, now over 2 hours. It shows it is not being blocked, but that it is blocking another. My questions:Does the 9 rows with the same ProcessID mean this statement is being run in parallel? Is the CXPacket wait on the first row mean parallelism is hanging the statement? Would running this using the MAXDOP hint provide a troubleshooting benefit?Is there more information you need to provide more light to the situation?Wed, 11 Apr 2012 10:22:38 GMTCraigmeisterhttp://www.sqlservercentral.com/Forums/Topic816736-360-1.aspxFor the past 2 months on my sql server 2005 Active/passive 64 bit cluster I have had the server buried at least once every week but system processes related to this command WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeoutThis table usually had only a few records, but this command still buries the server. Anyone seen anything like this before?Tue, 10 Nov 2009 12:05:33 GMTtimscroninhttp://www.sqlservercentral.com/Forums/Topic1280780-360-1.aspxI have a stored procedure with a number of parameters - the important one is @in_forename Nvarchar(35)In the stored procedure the code is IF (@in_forename IS NULL) SET @in_forename = '%%';ELSE if (@in_forename = '') SET @in_forename = '%%';ELSE SET @in_forename = @in_forename + '%';it then uses the @in_forename in the queryif @in_forename is '%' the query returns 2+ million rows in 1 minutes 6 seconds, If @in_forename is null it returns in 6 mins + and if @in_forename is '' it takes 20 minutes.For each run it uses a different query plan and only for '%' is there are degree of parallelism.Any suggestions?Tue, 10 Apr 2012 07:28:02 GMTjim.davidhttp://www.sqlservercentral.com/Forums/Topic1281330-360-1.aspxi have one doubt regarding the scheduled backupmaintenance plan jobs during 10 am in one sql server production environment maintenance plan backup jobs are scheduled by this daily backup is executed if it is failed.Manual backup is fired (or) executed normally 1.in which cases this backup job can be failed 2.what are the frequencies followed if scheduled backup maintenance jobs fails 3. even though there is no error logs in event viewer and in sql server error logs backup is not available on particular date 15/04/12 morning 10 am scheduled backup job also not failed but backup is not generated. y it can happenTue, 10 Apr 2012 23:41:56 GMTnaga.rohitkumarhttp://www.sqlservercentral.com/Forums/Topic1274341-360-1.aspxHello SQL Experts,I need help on identifying the performance of a procedure at different time(hours) of the day.When I execute this procedure by 10 AM it produces the expected result in 2 secs. The same procedure when executed at 11+ AM produces the expected result in ~25 secs. Note: The test was carried out with the same amount of data in the affected table(s).Now, I need to frame a test script to execute this procedure every hour and check if the procedure's execution time is exceeding the threshold time limit. if yes, then at the same time, I need to query the database for the running jobs or any other executions (you can suggest) and list them in the output.I need to make this test to identify,1. At what hour of the day, the execution takes more time ~ slower performance2. At what hour of the day, the execution takes lesser time ~best performance3. what are the jobs that are running in the SQL server during the slower performance hour.4. Is there any of the job that runs during the slower performance hour tries to call this procedure.p.s: our environment is clumsy with too many databases and Also, I dont have admin privilege over the given server to perform certain actions or to execute some queries. Please consider this too.I was posting this help after trying out different options (known to me) and eventually everything failed :(Thanks in advance!!!! :-)Wed, 28 Mar 2012 08:21:00 GMT4nvinothhttp://www.sqlservercentral.com/Forums/Topic635117-360-1.aspxHi,While running maintenance plan for reoranizing the index for user database we are getting the following error"Failed:(-1073548784) Executing the query "ALTER INDEX [PK__EventStage__46AF6B36] ON [Event].[EventStage] REORGANIZE WITH ( LOB_COMPACTION = OFF )" failed with the following error: "Cannot find index 'PK__EventStage__46AF6B36'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."Please help us to resolve this issue.The error occurs while reorganizing the index for operations manager database.Regards,Karthikraj.LMon, 12 Jan 2009 16:31:43 GMTlkarthikraj-609584http://www.sqlservercentral.com/Forums/Topic1280713-360-1.aspxHow to search Words From row using SQL Query?Tue, 10 Apr 2012 05:30:22 GMTAjith 42213http://www.sqlservercentral.com/Forums/Topic1280065-360-1.aspxHi,When I checked the sp_helpdb, came to know that SQLServer serives were restarted [tempdb re-created] and there was no server rebooted.Found the logs as below.Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.Please advise how to find who restared the services and what might be the cause behind this change?Thanks and Regards,Ravi.Mon, 09 Apr 2012 03:46:32 GMTravisamigohttp://www.sqlservercentral.com/Forums/Topic1277402-360-1.aspxI am having servere performance issues on a table I have inherited (it's never MY fault!):-DThe table holds balances and some other details for approx 3.7M accounts and is written to 4 times a week.Each time data is written to the table, some more accounts are added but as far as I can tell we never stop writing balances even when the account is cleared.The columns of interest are AccountID,UpdateDate, SubBal-A, SubBal-B,...SubBal-KCurrently the table has an index on AccountID only and an index on UpdateDate only.both indexes are non-clusteredI have a query with performs well on my development data (200K accounts, 4 files of balances) with an additional index (Unique) Acc_no,UpdateDate - Time to query approx 11 seconds This also performs adequately on a separate database of live data (3.7M accounts, 4 files of balances) with the extra index - time to query approx 7 minutes - which I will live withSo far it has been running 52 minutes on the production server and not returned me any records.The estimated query plan indicates that it is doing a table scan of all 476M records which is clearly what is killing it!I am trying to find all accounts for a given date where the account either did not exist on the previous file or one or more of the subBals has changed.The script is below: tables and fields have been anonymised and the number of comparitors reduced for brevity[code="sql"]DECLARE @Date as datetimeset @Date = '20120330';;WITH DATE_RANK as(-- Get the processing dates SELECT distinct update_date FROM Balances),DATE_RANK2 AS(-- rank the processing dates so that we can link to the next processing date SELECT update_date, RANK() OVER (ORDER BY UPDATE_DATE DESC) as 'Rank' FROM DATE_RANK),CTE as(-- Get the records for today and join to those on the previous run: -- use the date_rank2 to do this so that I can find the previous date where they are non-contigious-- D = today's detail records-- P = Previous run Detail records SELECT D.Acc_no, D.Update_date, P.Update_date as 'Prev_update_date', D.SubBalA, D.SubBalB, D.SubBalC, D.SubBalD, FROM Balances D join Date_rank2 DRD on DRD.update_date = D.Update_date left join -- get the previous date Date_rank2 DRP on DRP.rank = DRD.rank + 1 left join -- get the records for the previous date Balances P on P.Acc_no = D.Acc_no and P.Update_date = DRP.Update_date WHERE D.update_date = @date and ( -- Compare current to previous (if no prev, then at least one SubBal is not zero) D.SubBalA <> coalesce(P.SubBalA,0) OR D.SubBalB <> coalesce(P.SubBalC,0) OR D.SubBalC <> coalesce(P.SubBalC,0) OR D.SubBalD <> coalesce(P.SubBalD,0))-- insert the changed recordset into a new shorter table that only contains records which are different from -- their predecessorinsert into ChangedBals( Acc_no, update_date, SubBalA, SubBalB, SubBalC, SubBalD, )Select Acc_no, update_date, SubBalA, SubBalB, SubBalC, SubBalD, from CTE[/code]I am thinking that the cause of the table scan is that it is having to compare the balance fields which are not part of an index. Would performance improve if I were to put them into a covering index - a new index will take many hours to build due to the size of the table so I don't want to be wasting my time with it. (plus it requires a change request), or do I just need my extra index of Acc_no/UpdateDateOr something else (i.e. an entirely new approach) The plan is that once I have the relevant records from the gigantic table we can truncate it because all the info we need will be in changed balances.Tue, 03 Apr 2012 09:13:05 GMTaaron.reese