InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 17 May 2008 09:16:04 GMT20http://www.sqlservercentral.com/Forums/Topic502444-360-1.aspxHi All,Having database spreaded across several files is it enough for SQL Server to assign separate worker thread per file if all the files are in the same filegroup, or do I need to put files in separate filegroups ? In other words - does SQL assign worker thread per file, or per filegroup ? Having 4+ CPU machine and database in 4 files located in 2 filegroups does SQL do parallel file operations using 2 or 4 processors ?Regards,SlawekFri, 16 May 2008 19:37:28 GMTSlawekhttp://www.sqlservercentral.com/Forums/Topic502210-360-1.aspxI've got a big table. A VERY big table. 20.2 billion (yes, Billion) rows, 2.7 Terabytes.It's got a clustered index on (date column, uniqueid)Table is partitioned by month.If I run the following query:select col1 ,count(col2) as count_rec ,avg(col2) as avg_recfrom BIG_tablewhere date_col>= '01-MAR-2008' and date_col < '1-apr-2008'group by col1order by col1it takes between 2.5 hours and 3.5 hours to run, scanning about 900 million rows (the approximate number of March records).If I copy those same March records into a separate heap table and run the same query, it completes in about 18 minutes.Since the BIG_table has a partitioned, clustered index on the date column, I would expect this to require almost exactly the same number of I/Os as the scan of the small, heap table. Even if the BIG_table was NOT partitioned, I'd still expect the clustered index to successfully limit the required I/O so that there was negligible difference between the performance of the two queries.Have I missed something obvious, or is anyone else puzzled by this?Fri, 16 May 2008 09:56:34 GMTPhilip Yalehttp://www.sqlservercentral.com/Forums/Topic501123-360-1.aspxGood Morning, I need some advice on what to bring up at a meeting with some developers concerning the performance issues they have been experiencing, they said its to do with indexing issues (don't know exactly what their concerns are at the moment), but thats the information I have so far. Do I suggest monitoring the servers using perfmon or advice on issues such as clustered indexes, included columns etc.Thu, 15 May 2008 03:23:47 GMTfoscohttp://www.sqlservercentral.com/Forums/Topic498671-360-1.aspxHi,I have a web app, which gets hit about 600 times per minutes. In each hit it performs a DB update query. Normally, the time it takes to perform the query is very fast. However, almost like clockwork, about every 7 minutes, the performance takes a hit and the query can take a couple of seconds to complete. I have also noticed that if the rate of hits rises, then the interval of the "performance hit" decreases (i.e., if I get 1200 hits per minute, the performance problem shows up about every 3 minutes). Thus there seems to be a connection between the performance hit and the number of queries performed. Sort of like SQL Server is doing something, which downgrades performance very briefly after every 4000 queries.I suspected that the automatic update of index statistics might be kicking in, but turning that off made no difference.I'm open for ideas...Especially if there are any logs, perfmon counters that might be helpful, please let me know.Thanks,JamieMon, 12 May 2008 05:31:05 GMTjamiejuliushttp://www.sqlservercentral.com/Forums/Topic501116-360-1.aspxHello all,I'm not very experienced in analyzing a Profiler Trace. Therefor I want to ask you to clear something for me.I've created a trace of a short process to see if we can improve this. The process contains several queries in which we use the hint "with (nolock)". But in the trace-results I see quite some "lock:acquired" and "lock:released".How can I find out more details about this "lock:acquired"? I want to know on which table and what type of lock (row/page/table) is acquired.Kind regards,HansThu, 15 May 2008 03:06:53 GMTHanShihttp://www.sqlservercentral.com/Forums/Topic501318-360-1.aspxPlease help me ..I dont know why the CPU is hike. My understanding When first time( 40 sec) we run the SP it loads all the data in the cache after execution. Second / 3rd time with same parameter if I run the same Sp it takes lots of time approx 2 minutes. Why should it?Query and explain is attached.:)I know lots of sorting is going on :My Perform says : Pages/Sec it shows 0.00Avg. Disk Queue Length: 30 %% Processor Time 98-99 % why ?Checked sysprocess and enable dealock graph no locking info !!I am applying SP2 now on the server ..hope this resolve. Please send me suggestions.Thanks inadvance !!! Thu, 15 May 2008 07:51:20 GMTMinaz Aminhttp://www.sqlservercentral.com/Forums/Topic499118-360-1.aspxBelow is a simple query that is taking 2 hour 30 min to run. I created clustered index on rowid and Non Clustered Index on BillingType and CobrandCode columns. But there is no luck.In the execution plan before the indexes it's a full table scan and after I created the indexes it is giving Clustered index seek. Please give me suggestion to optimize this.SelectRowID,DID,AccountDID,ContractNumber,InsertionOrder,CreateDT,ActivateDT,CreatedByID,ModifyDT,ModifiedByID,Comment,CompanyName,StatusCode,TotalRetailAmount,TotalActualAmount,DeletedRetailAmount,DeletedActualAmount,BillingType,BillingCycle,InvoiceDeliveryMethod,ValidateStatus,ValidateDT,ValidatedByID,CancelStatus,CancelDT,CancelledByID,CancelReasonCode,AllowRepChange,TotalCreditedAmount,SystemModifiedDT,RenewableFlg,RenewalDuration,RenewalChangedByID,RenewalChangeDT,RenewedFlg,DiscountPct,CoBrandCode,TrackingCode,TrialDealFlg,BizOwnerCode,CustomerRenewalFlg,CCAddress,CCCity,CCCountry,CCExpiration,CCName,CCNumber,CCState,CCType,CCZip,ContactName,ContactPhone,PaymentMethod,BillTrackingCode,BillEmail,BillPhone,BillName,BillAddress1,BillAddress2,BillCity,BillState,BillZip5,BillZip4,BillCountry,BillProvince,BillPostalCode,AccountLevel,MasterAccountDID,InvoicingFlg,ExternalAcctID,BillingInterval,ProductStartDT,ProductEndDT,PackageDID,ContactAddress1,ContactAddress2,ContactCity,ContactState,ContactZip,ContactCountry,ContactEmail,ContactFaxFrom ARBkup.dbo.HHContract200804 where BillingType <> 'KNEE' and CobrandCode <> 'CBNH_KNEE'Mon, 12 May 2008 14:18:42 GMTSanjuhttp://www.sqlservercentral.com/Forums/Topic500502-360-1.aspxGuys,I was trying track wait types which are causing blocks and eventually timeouts, I found the possible wait types. The wait type that was causing timeouts in LCK_M_S. The listed below are lock wait types, are there implications of Latch Wait types on the performance.DO you guys know what are the implications of each wait types and which wait type needs to be monitored.Any suggeestions/inputs would help.LCK_M_SCH_S LCK_M_SCH_M LCK_M_S 0x03 LCK_M_U 0x04 LCK_M_X 0x05 LCK_M_IS LCK_M_IU LCK_M_IX LCK_M_SIU LCK_M_SIX LCK_M_UIX LCK_M_BU LCK_M_RS_S LCK_M_RS_U LCK_M_RI_NL LCK_M_RI_S LCK_M_RI_U LCK_M_RI_X LCK_M_RX_S LCK_M_RX_U LCK_M_RX_X ThanksWed, 14 May 2008 07:56:23 GMTamhttp://www.sqlservercentral.com/Forums/Topic501097-360-1.aspxHi,I am trying to reorganize all indexes on a database, but for some indexes I get the following error:The index "ABC" (partition 1) on table "XYZ" cannot be reorganized because page level locking is disabled.Now I know to enable page locking I execute: ALTER INDEX ABC ON XYZ SET (ALLOW_PAGE_LOCKS = ON)My questions are:Why was page locking disabled in the first place? (SQL sets this option automatically right?)What is the effect on my data and performance if I do Enable page locking on all indexes?Any help please?MThu, 15 May 2008 02:27:19 GMTmarissavhttp://www.sqlservercentral.com/Forums/Topic498381-360-1.aspxI've following table and datatblSELDate_Taken | Time | Main_ID | Value-----------------------------------------------5/11/2008 12:00:00 AM | 500 | 233 | 47805/11/2008 12:00:00 AM | 500 | 239 | 31225/11/2008 12:00:00 AM | 515 | 233 | 54355/11/2008 12:00:00 AM | 530 | 233 | 67995/11/2008 12:00:00 AM | 545 | 233 | 40905/11/2008 12:00:00 AM | 600 | 234 | 23125/11/2008 12:00:00 AM | 615 | 233 | 7409.........tblSEL having more than 6million rows[i]*Date_Taken datatype is smalldatetime[/i]tblLocationMain_ID | Location---------------------233 | PRK234 | PER239 | SAB.........i've following query to getting the last 7month dataSELECTt1.Date_Taken, t1.Time,t1.Main_ID, t1.WATER_ULEVELFROM dbo.tblSEL t1 INNER JOIN dbo.tblLocation t2ON t1.Main_ID=t2.Main_IDWHERE t2.Location='PRK' AND t1.Date_Taken>=CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101) ANDt1.Date_Taken<=CONVERT(VARCHAR(10), GETDATE(), 101)ORDER BY t1.Date_Taken, t1.TimeHow to adjust this query to make sure i get same data and also run at the best performance.Sat, 10 May 2008 10:21:31 GMTSharul Nizamhttp://www.sqlservercentral.com/Forums/Topic500541-360-1.aspxwe have a Database in 2000 and accessing it using Management Studio.I detached this DB and attached to a Test Server.I changed the Compatibility Level from 80 to 90.Can anyone please tell me what are the validations for this scenario.What are the others thing that i need to look for for my DB to perform well.Aspirant DBAaspirant.dba@gmail.comWed, 14 May 2008 08:19:52 GMTaspirant.dbahttp://www.sqlservercentral.com/Forums/Topic500482-360-1.aspxI inherited two databases that are at compatibility level 65. These databases are on SQL 2000 server. What is the best way to move these databases to level 80. These are our document file server databases so they are both very large and important databases.Wed, 14 May 2008 07:43:13 GMTErinhttp://www.sqlservercentral.com/Forums/Topic500025-360-1.aspxHi, there are about 9000 error message codes, in SQL 2000 the most critical were about 130 (Severity 20-25)I will be using a monitoring app that will send alerts based on the SQL Error codes[b]In SQL 2005 what are the most critical errors to monitoring?[/b] I know that the answer depends on different scenarios, but let me know your experiences in real world scenarios.RegardsTue, 13 May 2008 15:31:53 GMTSerchhttp://www.sqlservercentral.com/Forums/Topic496864-360-1.aspxHi there! I have an application that uses MS SQL 2005 Server database engine and have about 10 million records. Each day it grows. I thought to improve database's performance and bandwidth by using clusterization, and after invistigating some books and articles I understood that clusterization and improving performance have nothing in common. Everyone says that there is no any performance's improvement after adding clusters BUT what about database's bandwidth? What I need is to improve bandwith by adding clusters and I think that if now my database handle for exmaple 100 queries in a second, after adding new cluster it should handle may be not per 2 times, but 1.5 times more. Am I right? Any thoughts about improving database's bandwidth? Thank youWed, 07 May 2008 23:27:55 GMTnKognitohttp://www.sqlservercentral.com/Forums/Topic498708-360-1.aspxi have created table with nonclusted index ,At the time of transaction one transaction id locking this table by another session id . but if create clustered index on it , it is not happeing . can anyone explain the reason behind this locking ? Thanks in Advance .Mon, 12 May 2008 06:34:03 GMTsudhakarahttp://www.sqlservercentral.com/Forums/Topic498073-360-1.aspxhi guys, i have sql server with a front end .net application. Now I am trying to figure out why the application is running very slow. I already determined that it is nothing on the db side, eventhough a few indexes should be rebuilt but i am having no blocks. Cpu usage is good but paging seems to be the problem from time to time it goes very high. now i ran the profiler and i see in the column event class: audit logout, application class: .net sql client data provider , reads: many rows above 10000000, so i think this has to be the problems, i have many rows that show this high amount of reads for the same event class and application class, any ideas of how to troubleshoot this?Fri, 09 May 2008 10:32:05 GMTDBAhttp://www.sqlservercentral.com/Forums/Topic499139-360-1.aspxhello gurus!, how do i find out my min and max pool size of my database?Mon, 12 May 2008 14:46:46 GMTDBAhttp://www.sqlservercentral.com/Forums/Topic500043-360-1.aspxHi me againI was executing PSSDIAG on one of our heavy volume OLTP platforms and found the following block information in the blocker scriptDate Time = 2008-05-09 10:02:14.263 Interval = 389.936 secondsBlocked Spids = 52,53,64,75,131,202,108Number Of Head Blockers = 1Spid 7 is blocking ??? --> Row not found in SysprocessesLast Query = Sysprocesses DetailsCommand = CHECKPOINTOpen Transaction = 0Status = runnableWait Resource = Wait Time = 0Wait Type = Not currently waiting on anything (0x0000)Last Wait Type = WRITELOG Resource DetailsCPU = 0Last CPU = 0CPU Difference = 0Physical IO = 184974Last Physical IO = 184974Physical IO Difference = 0Memory Usage = 0Last Memory Usage = 0Memory Usage Difference = 0I understand that this is a in the runnable queue and not the running queue. I am suspecting that the CHECKPOINT is causing the block ? .I understand that the recovery model can play a huge part in CHECKPOINTS and it is currently on simple recovery. The CHECKPOINT can also be affected by latches and if the the log bytes flushed and the disk writes of the CHECKPOINT cannot fit into a 8 kb page (still trying to get more details on this from sql 2000 and 2005 IO basics)Any comments will be appreciated. Tue, 13 May 2008 16:17:43 GMTCoetzeeWhttp://www.sqlservercentral.com/Forums/Topic498262-360-1.aspxI have a database whose size increased from like 28000 records to 800K records ansd searching in this database takes about 18 seconds which is not a pleasure, can anyone guide me on this... 28K database used to take 1-6 seconds.Let me know what else should I put here to make you understand betterOne example of the query I am using on the view is:select BibNumber from [vw_TypesOnlinePeriodicalsJoin]where TiQueryValue like '%[^a-z]federal%' AND Type='nongov-ebooks'Order By TiOrderByValue and view is like with ~800k recordsSELECT dbo.tb_OnlinePeriodicals.PID, dbo.tb_OnlinePeriodicals.BibNumber, dbo.tb_Titles.Title, dbo.tb_OnlinePeriodicals.Hypertext, dbo.tb_OnlinePeriodicals.Hyperlink, dbo.tb_OnlinePeriodicals.RestrictionText, dbo.tb_OnlinePeriodicals.DirectionText, dbo.tb_OnlinePeriodicals.Frequency, dbo.tb_OnlinePeriodicals.UpdatedDate, dbo.tb_OnlinePeriodicals.RestAccess * 1 AS RestAccess, dbo.tb_OnlinePeriodicals.PublAccess * 1 AS PublAccess, dbo.tb_OnlinePeriodicals.Multiple * 1 AS Multiple, dbo.tb_OnlinePeriodicals.InfoGuide * 1 AS InfoGuide, dbo.tb_Titles.TiQueryValue, dbo.tb_Titles.TiOrderByValue, dbo.tb_SubjectLists.SubjectList, dbo.tb_SubjectLists.SLQueryValue, dbo.tb_Titles.StandardRowNum, dbo.tb_Types.TypeFROM dbo.tb_OnlinePeriodicals INNER JOIN dbo.tb_Titles ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_Titles.BibNumber INNER JOIN dbo.tb_SubjectLists ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_SubjectLists.BibNumber INNER JOIN dbo.tb_Types ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_Types.BibNumber Post #498972 --archanaFri, 09 May 2008 16:35:20 GMTachaudhrhttp://www.sqlservercentral.com/Forums/Topic498590-360-1.aspxhi..My database will be suspect...how can i retrive the data in suspect database.Mon, 12 May 2008 01:49:39 GMTanilhttp://www.sqlservercentral.com/Forums/Topic499202-360-1.aspxHozit guysI have a question reagrding looking at certain event's in sql profiler. Background: Did a trace last week and found many sp:cachemiss in the profiler trace. Most of the statements are procedures that the execute a insert or delete or select command. When I reviewed the profiler indepth and I tested this on a sepearate machine with my own code I found that SQL server always does a sp:cachemiss then a sp:cachehit on the execution context(eventsubclass). Now I feel there is not realy a problem here since the same plan is being used just a different execution context, but why the sp:cahcemiss first ? What I found was: sp:cachmiss will show "Usp_Insert 'Wynand'"sp:cachehit (execution context with the same plan)All the stored proc's USp_Inerts (variable) do this and the delete and select stored procedure. The is nothing special about the insert proc (the delete is just as simple and the select proc)Insert into tablevalues(@1,@2,@3) can some one explain this ? I even do a batch with 100 executions of the same procedure and still sp:cachemiss and the sp:cachehitthankswMon, 12 May 2008 16:34:19 GMTCoetzeeWhttp://www.sqlservercentral.com/Forums/Topic498610-360-1.aspxHi there! I have a database's table that have about 10M records. Users retrieve this data for viewing or getting statistics information based on this table. The question is: in such number of rows what more effective: to retrieve data for viewing and getting statistics from single server or to use replication and retrieve data for viewing on original server, but statistics from subscriber server? What more faster? I am asking because the data retrival for viewing has bigger priority and need to be done very fast, but statistics are also important. ThanksMon, 12 May 2008 02:46:03 GMTnKognitohttp://www.sqlservercentral.com/Forums/Topic496284-360-1.aspxHow can i generate random unique number in sql server except newid() ?Wed, 07 May 2008 07:09:11 GMTsudhakarahttp://www.sqlservercentral.com/Forums/Topic495641-360-1.aspxlast weekend we had an upgrade, my data retrieval time from the replication server is extremely slow. a report that previously took maybe 15 seconds to return data is taking 5 minutes now. I have made no changes to the query.running on sql server 2005replication is working finecan any one tell me what might be the problemTue, 06 May 2008 08:04:46 GMTNicolehttp://www.sqlservercentral.com/Forums/Topic419448-360-1.aspxHi I have a very large dimension ( 18 M records) that needs processing.Currently the time taken to process the dimension is around 5 hours. I need to bring this down to 3 hours max .I am using the process update property and the cube is partioned. The dimension has 2 string attributes. I have read about the limits imposed by the string attribute on the dimension (4GB). but cant seem to find a suitable workaround. also the ASSTORE files are not exceeding the 4 gb limit , Frankly I am at a loss to try and explain this.Have installed the latest services packs as well.any advice on this would be a great help :)Tue, 06 Nov 2007 23:36:47 GMTJaykayhttp://www.sqlservercentral.com/Forums/Topic497764-360-1.aspxi am using table to populate data for report purpose . if i launch same report from two clients this table always locked by another user. Am using primary key is newid() so it always different . how can i avoid this lock ? why one spid locking the another session though key is different ? Thanks in Advance .Fri, 09 May 2008 05:47:39 GMTsudhakarahttp://www.sqlservercentral.com/Forums/Topic494722-360-1.aspxwondering if someone can verify something for me please.While trying to identify why a batch job had blown out by 6 hrs, I noticed that some of the process ids be identified as being blocked by itself.This is SQL Server 2K so in EM in the Locks/ProcessID tree when you expand it you would seespid 71 (blocked by 71)spid 71 (blocking)is this caused by parallelism??the machine is a 4 cpu Opteron 4GB RAM, WinServer 2003 R2 x64 sp2thanksSun, 04 May 2008 15:09:12 GMTjohn panhttp://www.sqlservercentral.com/Forums/Topic496111-360-1.aspxI need to load nearly 1.2 million records to one of my table. So, I drop the indexes and load the data to the table. When I try re-create the indexes, it is taking nearly one hour to create the indexes. I feel it is too long. I have attached the source code. Is there any way to speed up.Source Code:[font="Courier New"]ALTER TABLE RETDCUST_CUSTADDRADD CONSTRAINT [PK_RETDCUST_CUSTADDR] PRIMARY KEY CLUSTERED ( [CUSNO], [ADDRTYPE] ) WITH FILLFACTOR = 100 ON [primary] -- 02. IX_RETDCUST_CUSTADDR_HOMEAREACREATE INDEX [IX_RETDCUST_CUSTADDR_HOMEAREA] ON [dbo].[RETDCUST_CUSTADDR](HOMEAREA) WITH FILLFACTOR = 100-- 03. IX_RETDCUST_CUSTADDR_NAMADDR1CREATE INDEX [IX_RETDCUST_CUSTADDR_NAMADDR1] ON [dbo].[RETDCUST_CUSTADDR](NAMADDR1) WITH FILLFACTOR = 100-- 04. IX_RETDCUST_CUSTADDR_NAMADDR2CREATE INDEX [IX_RETDCUST_CUSTADDR_NAMADDR2] ON [dbo].RETDCUST_CUSTADDR(NAMADDR2) WITH FILLFACTOR = 100-- 05. IX_RETDCUST_CUSTADDR_NAMADDR3CREATE INDEX [IX_RETDCUST_CUSTADDR_NAMADDR3] ON [dbo].[RETDCUST_CUSTADDR](NAMADDR3) WITH FILLFACTOR = 100-- 06. IX_RETDCUST_CUSTADDR_NAMADDR4CREATE INDEX [IX_RETDCUST_CUSTADDR_NAMADDR4] ON [dbo].[RETDCUST_CUSTADDR](NAMADDR4) WITH FILLFACTOR = 100-- 07. IX_RETDCUST_CUSTADDR_WORKADDR1CREATE INDEX [IX_RETDCUST_CUSTADDR_WORKADDR1] ON [dbo].[RETDCUST_CUSTADDR](WORKADDR1) WITH FILLFACTOR = 100-- 08. IX_RETDCUST_CUSTADDR_WORKADDR2CREATE INDEX [IX_RETDCUST_CUSTADDR_WORKADDR2] ON [dbo].[RETDCUST_CUSTADDR](WORKADDR2) WITH FILLFACTOR = 100-- 09. IX_RETDCUST_CUSTADDR_WORKADDR3CREATE INDEX [IX_RETDCUST_CUSTADDR_WORKADDR3] ON [dbo].[RETDCUST_CUSTADDR](WORKADDR3) WITH FILLFACTOR = 100-- 10. IX_RETDCUST_CUSTADDR_WORKADDR4CREATE INDEX [IX_RETDCUST_CUSTADDR_WORKADDR4] ON [dbo].[RETDCUST_CUSTADDR](WORKADDR4) WITH FILLFACTOR = 100-- 11. IX_RETDCUST_CUSTADDR_WORKADDR1_WORKADDR2_WORKADDR3_WORKADDR4CREATE INDEX [IX_RETDCUST_CUSTADDR_WORKADDR1_WORKADDR2_WORKADDR3_WORKADDR4] ON [dbo].[RETDCUST_CUSTADDR](WORKADDR1,WORKADDR2,WORKADDR3,WORKADDR4) WITH FILLFACTOR = 100-- 12. IX_RETDCUST_CUSTADDR_HOMPHONECREATE INDEX [IX_RETDCUST_CUSTADDR_HOMPHONE] ON [dbo].[RETDCUST_CUSTADDR](HOMPHONE) WITH FILLFACTOR = 100-- 13. IX_RETDCUST_CUSTADDR_BUSPHONECREATE INDEX [IX_RETDCUST_CUSTADDR_BUSPHONE] ON [dbo].[RETDCUST_CUSTADDR](BUSPHONE) WITH FILLFACTOR = 100-- 14. IX_RETDCUST_CUSTADDR_POSTCODECREATE INDEX [IX_RETDCUST_CUSTADDR_POSTCODE] ON [dbo].[RETDCUST_CUSTADDR](POSTCODE) WITH FILLFACTOR = 100-- 15. IX_RETDCUST_CUSTADDR_POBXNOCREATE INDEX [IX_RETDCUST_CUSTADDR_POBXNO] ON [dbo].[RETDCUST_CUSTADDR](POBXNO) WITH FILLFACTOR = 100-- 16. IX_RETDCUST_CUSTADDR_POBXAREACREATE INDEX [IX_RETDCUST_CUSTADDR_POBXAREA] ON [dbo].[RETDCUST_CUSTADDR](POBXAREA) WITH FILLFACTOR = 100-- 17. IX_RETDCUST_CUSTADDR_PEGNHOLECREATE INDEX [IX_RETDCUST_CUSTADDR_PEGNHOLE] ON [dbo].[RETDCUST_CUSTADDR](PEGNHOLE) WITH FILLFACTOR = 100-- 18. IX_RETDCUST_CUSTADDR_MOBILENOCREATE INDEX [IX_RETDCUST_CUSTADDR_MOBILENO] ON [dbo].[RETDCUST_CUSTADDR](MOBILENO) WITH FILLFACTOR = 100-- 19. IX_RETDCUST_CUSTADDR_FAXNOCREATE INDEX [IX_RETDCUST_CUSTADDR_FAXNO] ON [dbo].[RETDCUST_CUSTADDR](FAXNO) WITH FILLFACTOR = 100-- 20. IX_RETDCUST_CUSTADDR_PAGERNOCREATE INDEX [IX_RETDCUST_CUSTADDR_PAGERNO] ON [dbo].[RETDCUST_CUSTADDR](PAGERNO) WITH FILLFACTOR = 100-- 21. IX_RETDCUST_CUSTADDR_MODIFDTE1CREATE INDEX [IX_RETDCUST_CUSTADDR_MODIFDTE1] ON [dbo].[RETDCUST_CUSTADDR](MODIFDTE1) WITH FILLFACTOR = 100[/font]Wed, 07 May 2008 01:37:03 GMTZahranhttp://www.sqlservercentral.com/Forums/Topic492345-360-1.aspxHello All,I have a perplexing situation and I was wondering if anyone had any input or ideas on this. I have a few views that are accessed by an application. The app has a 15 second timeout when querying these views. We've recently changed the views (not too much) and now the app times out when accessing the views. If I run sp_recompile on the views, the app doesn't time out for a minute or two then it goes back to timing out. I've done a full index rebuild, updated stats and recompiled. This is getting progressively worse as time goes on. Has anyone had a similar situation or any ideas?SQL 2005 EnterpriseSP 2Thanks in advance!RubrechtTue, 29 Apr 2008 11:08:06 GMTRuprechthttp://www.sqlservercentral.com/Forums/Topic495306-360-1.aspxWe can set max threads. We can turn on parallism.But I still find, some times, a process run wih single thread, some times with multiple threads. I noticed once, one process ran with over 40 threads.I noticed that, most time, a process only runs with several threads. CPU and memory usages were low, say couple percents. My question is:Is it possible to enforce higher number threads to enforce a process to use more CPU time and more memory and get results in a shorter period?Any input will be greatly appreciated.Mon, 05 May 2008 15:20:10 GMTSQL ORACLEhttp://www.sqlservercentral.com/Forums/Topic496581-360-1.aspxWe have a classic ASP web application (third party product) which uses a COM+ component for the middle tier. It primarily executes API cursors against the DB, which I think is only one of many issues.So far, I've collected data via PerfMon capturing the following counters;1. Cached Cursor Counts = 1560 (average)2. Cursor Memory Usage = 1149800 KB(average)3. Cursor Requests per sec = 5.2 (average)...max = 25!Here's are my problems...1. The Active Cursors counter shows 0 (zero).2. The sys.dm_exec_cursors DMV returns 0 results. i.e. SELECT * FROM sys.dm_exec_cursors(0)It is easy to see the cursor activity within profiler as RPC's. Why would the DMV and Active Cursors Counter not return any data?Any help is greatly appreciated. I have not been able to find a clear answer to this online.Wed, 07 May 2008 12:09:51 GMTJared Hunthttp://www.sqlservercentral.com/Forums/Topic495550-360-1.aspxHow can i see the data of particular row id ex:KEY: 8:72057605279514624(6d020e2b1505) this is key id i want see the data of this key . Thanks in Advance .Tue, 06 May 2008 06:26:45 GMTsudhakarahttp://www.sqlservercentral.com/Forums/Topic496225-360-1.aspxHello,first, I'm new here, so hello to everybody :-)I've looked around in SQL Server 2005 documentation and in this forum but could not get a precise answer yet.Is there a certain order the expressions in the WHERE-clause of a statement are processed?For example:1. In most programming languages a statement with two conditions and concatenated with AND is not fully interpreted if the first condition is FALSE. 2. Second case: [1 true] OR [2 true], condition 2 does not need to be interpreted because condition 1 is TRUE.But this seems to be not the case in SQL Server. Is there a way to define the order? The official documentation only says that the order of interpretation can vary. Or am I missing something?Thank you in advance!Wed, 07 May 2008 05:58:18 GMTjulianhttp://www.sqlservercentral.com/Forums/Topic495977-360-1.aspxHi,I'm looking at the waitresource in the sysprocesses as part of index & block troubleshooting (SQL2005)I see the following value (as an example):[code]KEY: 12:72057594386382848 (9701bf0d0dea)[/code]I understand it should be in the format (from http://support.microsoft.com/kb/224453):[code]KEY: DatabaseID:ObjectID:IndexID (Hash value for index key)[/code]I can confirm the '12' in this case is the DB id in question, but what of the remaining number?Clearly something isn't quite right. Any ideas for determining the index/key in question?thanks, MarkTue, 06 May 2008 15:04:28 GMTmarklegoszhttp://www.sqlservercentral.com/Forums/Topic490605-360-1.aspxHello,we have matrix stored like this:create table matrix( row int, column int, value float)Now we have to build a text representation of this data. Unfortunately the matrix has not 10 rows and 10 columns, but up to 100.000 columns and 10.000 rows.My first approach to build the string is very slow:Pseudocode:foreach row { foreach column { mystring += ', ' + valueAt(row, column) }}My second approach is as follows:1. build string for the first 100 columns and store itdo step 2. build string for the 101 to 200th column and concatenate it with the intermmediate result of step 1until finishedIt is more set-based and probably faster, but i haven't yet testet it with the full amount of data.My Question is:- is there a better idea?- would ssis be faster?What are the costs of an operation like: Select big_text + small_textTobiasFri, 25 Apr 2008 06:52:06 GMTtobe_hahttp://www.sqlservercentral.com/Forums/Topic495736-360-1.aspxHi,I just started to use Execution Plan.But I have difficulty to understand it.In my Execution Plan I have:Table Scan, Clustered Index Scan, Key Lookup, Hash Match, Filter, Nested Loops,etc.What are the bad signs?For example, Clustered Index Scanis 83%. Is it good or bad?Tue, 06 May 2008 09:27:33 GMTriga1966http://www.sqlservercentral.com/Forums/Topic495785-360-1.aspxHope someone has done this beofre.I am looking for a way to validate an MS SQL 2005 installation so that is does not present "performance bottlenecks" when an application and/or data is introduced to an environment. Basically, are there any microsoft tools that can be used to ensure that the installation of SQL is performing optimally.Your help is much appreciatedTue, 06 May 2008 10:30:06 GMTiamhoochhttp://www.sqlservercentral.com/Forums/Topic430326-360-1.aspxHi all, I don't know if it can be usefull for you to understand the problem but, this is the configuration I havephysical server : - 4 proc dual core - 8Go RAM - windows 2003 Server Ent. SP1 - Virtual Server 2005 R2 (with 2 virtual servers)Virtual Server (the one with I have problem...) : - 3721 Mo - 1 proc (no choice...) - Windows 2003 Server Ent. SP2 - SharePoint Portal Server 3.0 - SQL Server 2005 SP2The second virtual server is up but not used by anyone (test server)My problem is : a colleague has installed this sharePoint server (so I don't know excactly how he did it) and we have a problem : the CPU is always 100% used... it is the sqlservr.exe process which take those resources. I have activated traces and I have a "Missing Column Statistics" for every SPID... The message is : NO STATS:([#AnchorText2].[LinkId], [A].[SourceDocID], [A].[LinkId])I have also messages (for the same SPID) which deal with tempdb. if I run this command : DBCC SQLPERF(LOGSPACE)the result for tempdb is : 49,99219 (size) 53,61775 (used) 0 (status)Somes options in the tempdb properties : - auto create statistics : True - auto shrink : False - auto update statitics : True - auto update statistics asynchronously : FalseI don't know if the problem deals with SQL or SharePoint... or both... I'm a begginer in "database administration" (as you can see) so any advice would be very appreciable.Thanks in advance. VincentThu, 06 Dec 2007 10:57:43 GMTvherinhttp://www.sqlservercentral.com/Forums/Topic493087-360-1.aspxI faced an strange issue, i debug it down to the lowest level, simplified....here is detail...One table with only one column having 80 million rows, try to insert that data in another table with same one column(not select into method) and log file grows to 17 times than the data...insert into pullVendorselect id from tvendorfacts: pullVendor is empty table and have id column with same data type as tvendor(numeric (19,0))database is in simple log recoverysql 2k5 std edition with 64 bit sp2 and 4gb ram, quad core (i tried same thing on sql s2k also same logging behavior)total reserved data size of tvendor table is 1.2 GBselect into just works fine, no issues, insert into another table creates log file to 20 GB+so overall it looks like sql server behaves strangely while handling 80 million rows, no logic suggests a simple insert of 1.2 gb data created log of 20+ GBTried and tested with many options and settings, ensure database is not corrupt also This is the first time I am handling such no of rows thus just wanna check if anybody think this is not a bug and something can be done to fix this logging issue...Awaiting your responses...Thanks....Prakash:)Wed, 30 Apr 2008 09:45:45 GMTPrakash Hedahttp://www.sqlservercentral.com/Forums/Topic494685-360-1.aspxHi,Proper Case caps the first letter of every word in sql server 2005.is there any function for this req ? Thanks in Advance .Sun, 04 May 2008 04:57:05 GMTsudhakara