SQLServerCentral / SQL Server 7,2000 / Performance Tuning

SQL Memory utilization & Perfromance

syam_pydimarri86 — Fri, 04 May 2012 01:33:03 GMT

Hi,I am using SQL server 2008R2 STD edition and my server configuration is win 2008R2 STD Edition 64bit and Dual core Processor with 8 GB of RAM, but recently i noticed that my CPU utilization is almost 70% and Physical memory utilization is also around 6 GB, so can any one suggest how to do minimize both CPU and Physical memory utilization and do performance tuning can any one help in this

Query Cost 27%

Alone — Tue, 15 May 2012 13:08:30 GMT

How to change non clustered index scan to non clustered index seek?The concerned tables already have non clustered index configured.

stored procedure running slow

karthik srinivasan — Fri, 04 May 2012 09:40:28 GMT

Hi My development and test databases are hosted on the same box. A stored procedure that runs for about 10 minutes in development database is running for about 4 hours in the test database.Could you tell me how to root cause this performance issue and fix?Thanks in advance.

Identfying long running queries in SQL 2000.

CH&HU-1021641 — Wed, 25 Mar 2009 00:17:41 GMT

Dear all,We are using SQL Server 2005 for production and the database compatability level is kept for 80 not 90. I have a qery for finding the long running queries which supports on SQL 2005 not 2000 database. Its throwing error when i run the queryto identify the long running queries.Query : select top 50qs.total_worker_time / execution_count as avg_worker_time,substring(st.text, (qs.statement_start_offset/2)+1,((case qs.statement_end_offsetwhen -1 then datalength(st.text)else qs.statement_end_offsetend - qs.statement_start_offset)/2) + 1) as statement_text,*fromsys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as storder byavg_worker_time descERROR : Server: Msg 170, Level 15, State 1, Line 11Line 11: Incorrect syntax near 'apply'.Can anyone please have a look into this to work fine for 2000 compatibility databases. Or else anyone please provide me the query for identifying the long running queries.Thanks,CH&HU.

Performance Measuring Tool

Stylez — Fri, 20 Apr 2012 09:19:22 GMT

Good Morning To All :)i would like to ask if there is a 3rd party tool that we can use to measure the Execution of a Stored Procedure. and would point which part of the SP is causing the performance issue? and at the same time will suggest possible code change to the user to correct the Performance issue? Thank you very much !

Computed Column is slowing down performance on a simple select statement

crackedcornjimmy — Wed, 04 Apr 2012 19:11:54 GMT

Background:Previously, my company was using a User Defined Function to html encode some data in a where clause of a stored procedure. Example below:[code="sql"]DECLARE @LName --HTML encoded last name as input parameter from userSELECT * FROM (SELECT LName FROM SomeView xtra WHERE (( @LName <> '' AND dbo.EncodingFunction(dbo.DecodingFunction(xtra.LName))=@LName) OR @Lname=''))[/code]I simplified this for clarity sake.The problem is, when the stored procedure with this query was called 45 times in quick succession, the average performance on a table with 62,000 records was about 85 seconds. When I removed the UDF, the performance improved to just over 1 second to run the sproc 45 times.So, we consulted and decided on a solution that included a computed column in the table accessed by the view, SomeView. The computed column was written into the table definition like this:[code="sql"][LNameComputedColumn] AS (dbo.EncodingFunction(dbo.DecodingFunction([LName])))[/code]I then ran a process that updated the table and automatically populated that computed column for all 62,000 records. Then I changed the stored procedure query to the following:[code="sql"]DECLARE @LName --HTML encoded last name as input parameter from userSELECT * FROM (SELECT LNameComputedColumn FROM SomeView xtra WHERE (( @LName <> '' AND xtra.LNameComputedColumn=@LName) OR @Lname='')[/code]When I ran that stored procedure, the average run time for 45 executions increased to about 90 seconds. My change actually made the problem worse!What am I doing wrong? Is there a way to improve the performance?As a side note, we are currently using SQL Server 2000 and are planning to upgrade to 2008 R2 very soon, but all code must work in SQL Server 2000.

SQL SERVER WEIRD Performance problem. Please help.

bullo — Mon, 02 Apr 2012 16:02:41 GMT

Hi, thank you for reading my first post.I’m migrating a Classic ASP app from ACCESS to SQL Server. I RE-wrote queries, [u]I’m not using Access with Linked Tables to SQL SRV.[/u] I have a page that is almost 10 times slower than the Access version of the app!!! It is a soccer school. The page is to Staff Coaches to Classes. It has VERY SIMPLE queries, but each one is repeated many times (one per coach). For example:1) Main query gets all coaches that are ACTIVE. All the rest happens inside this loop2) For each coach on the loop, I query the DB to check it he/she is available for the class (date and time)3) If available, I have another query that check if she/he has any VACATION request (thus voiding availability).4) If coach has no vacation, I query the DB again to see if coach if already staffed in a conflicting class (date/ time overlaps the class im searching) 5) If not in a conflicting class, I list the coach as available.6) Cycle repeater per coach.All tables have proper indexes (I rebuilt them). When running any query listed above isolated to test latency, SQL Server runs it faster than access. Im using SQL Express 2008 for development. I even tried with an INSTANCE that is on another computer. Same problem. I even tried migrating tables used in this page to MySql just to see performance, and it works wonderfully. I don’t think that a stored procedure helps here. The logic is on the page as explained. Queries are simple and work ok, so no bottle neck hereDoes any want know what is going on? I’m searching for weeks.In Performance monitor I see there are many batch req /sec and compilations per sec.looks like Sql Server doesn't like to compile many many queries in a short time, even being simple queries?I REALLY appreciate any help.Thank you!Munscio

Session times overruning for CPU

jblovesthegym — Thu, 29 Mar 2012 10:39:50 GMT

Hi all,We have a tool that collects stats, and today about 4 sessions have excessive lengths (6-7,000 seconds), 2 of which are regular (noticed them yesterday too)We do not have any complaints but this is part of an overall cleanup of our environment. I presume these are the session wait times, any ideas/resources on how to ID+resolve?Thanks, JB

parallel inserts for data analysis (ie very large not many small transactions)

johnb4801 — Wed, 21 Mar 2012 10:07:42 GMT

It is clear that sql 2008 r2 will not automatically parallelize inserts - it will only automatically parallelize the select part of those inserts. Have they improved this in sql 2012 ?Large Inserts from one table to another are much faster generally when tablock is used. Is there anything better - maybe something that turns of all logging ?It is possible to manually parallelize inserts of course but then tablock wont work so I found I had to use many parallel inserts to equal the speed of one insert with tablock with a typical query. Partitioning did not seem to speed up parallel inserts. Anybody have better ideas on manual parallelization ?

Most common types of fix...

Jake Shelton — Mon, 19 Mar 2012 14:20:54 GMT

Hi all, I've been taking a look around the Perf fora recently and comparing the scenarios to my own findings, and I was curious as to which types of solution you all have found are the most prevalent. I'd say the following types of fix are most common, so imo, in descending order;1) Code rewrite;2) Memory/CPU upgrade;3) SQL Server reconfiguration;4) Disk System upgrade/reconfiguration5) OS reconfiguration6) Server/Service restartLike I said, I'm curious to know if this agrees with (or how this differs from) your own experiences.

Access 2007 very slow with SQL server odbc connection (sql 2000)

tw_fake — Thu, 28 Feb 2008 01:24:53 GMT

We recently bought some new pc's with office 2007. Before we only had office 2000 and 2003. We use a mdb with a SQL server odbc connection and this works very well with access 2000 and 2003, but with access 2007 it's very slow.If we create a new record, just typing is extremely slow. Anyone has some suggestions how to resolve this?Thanks in advance,Tw

Procedure sp_recompile

ravisamigo — Tue, 06 Mar 2012 04:09:28 GMT

Hi,Please someone advice me on this.SQLServer 2000----------------Server: Msg 15009, Level 16, State 1, Procedure sp_recompile, Line 18The object 'function1' does not exist in database 'database1.The 'function1' exists in database 'database1' when I checked.Thanks and Regards,Ravi.

how to Eliminate 'IN'

KcV — Thu, 01 Mar 2012 01:18:43 GMT

Hi,How can i eliminate IN from following query ?select * from idata a where isnull(validationstatus,'Y')='Y' and srno not in (select srno from IFDetails where eby =1 ) and edate between '20.02.2012' and '20.02.2012'this query takes too long time due to both tables having more then 1.5 million rows.thanks

Trace Flags 1204 and 1205 output changed?

KitKaterina — Thu, 31 Jul 2008 11:56:52 GMT

Hi AllI'm a long time lurker - this is my first post, so hello and I hope you can help! :DI have just enabled trace flags 1204 and 1205 on a new server, which is at SQL Server 2000 SP4. Funny thing is I am not seeing the output which I expected to see in the event log, I'm not seeing KEY, TAB or PAG references, and also nothing from the buffer. Has anyone else experienced anything similar? Has anyone got any ideas how to get all the detail back in the output from these trace flags?

SQL Server Performance Monitoring Tools

eccentricDBA — Thu, 21 Oct 2010 12:42:12 GMT

The following is a short list of tool I found performing a quick search on the web and I was curious if anyone had any experiences with these tools or could recommend other tools.Redgate SQL Response ( http://www.red-gate.com/products/SQL_Response/index.htm )Idera SQL Diagnostic Manager ( http://www.idera.com/Products/SQL-Server/SQL-diagnostic-manager/ )embarcadera DB Performance Center XE ( http://www.embarcadero.com/products/performance-center?gclid=CI_hzsvC5KQCFVvW5wod-R-WMg )Confio Ignite ( http://www.confio.com/ppc/FreeTrial-SQLServer.php?source=GoogSqlS&gclid=CKOcoM3C5KQCFYK5Kgod4zxJMw )Quest Spotlight ( http://www.quest.com/spotlight-on-sql-server-enterprise/ )Heroix Longitude ( http://www.heroix.com/agentless/agentless_database_monitoring.htm )SQLSentry ( http://www.sqlsentry.com/ )Personally, I have been working with Idera SQL Diagnostic Manager. However, it has been a while since I have researched what tools are available.Any suggestions or comments would be appreciated.

Sql Server Performance

suresh0534 — Wed, 18 Jan 2012 16:33:01 GMT

Hi, If we create Temporay Tables in a Stored Procedure will degrade the performance of the query?Ex:CREATE TABLE #TempTable(id int,name varchar(50))INSERT INTO #TempTableSELECT id,name FROM EmployeeHow to improve the performance of stored procedure while using temporary tables?Thanks,Suresh

SQL SELECT STATEMENT PERFORMANCE ISSUE

shubham.saxena67 — Sun, 15 Jan 2012 22:54:19 GMT

Dear Friends,I am having two tables TB_PERSON_P and TB_PERSON_Rboth having a common column R_IDnow we can write two query:select R_ID from TB_PERSON_P P,TB_PERSON_R R where P.R_ID=R.R_IDandSelect R_ID from TB_PERSON_P P join TB_PERSON_R on P.R_ID=R.R_IDwhich would be faster among these two statement?Thanks!!

Memory Utilization is very low 13.8 GB out of 20 GB

Alone — Sat, 12 Nov 2011 11:23:52 GMT

Everyday I experience block in the database but checked query plan is ok ,all indexes already in place,no fragmentation issue..When i was looking into memory some thing is wrong:This a SQL dedicated server,it has sqlserver 2000 ent edition 32 bit installedTotal memory: 25 GB max memory setting--20 GBBut sql always using only 13.8 GB.AWE is enabled.Should i migrate to SQLSERVER 2000 ent 64 bit.I can't migrate 2005/2008 as application won't support..Pls suggest..

Buffer cache hit ratio always zero

jack 63767 — Tue, 08 Nov 2011 00:07:09 GMT

hi,I've saved my performance counter settings to a .htm-file. When I reopen the performance counters by doubleclicking on the .htm file the Buffer cache hit ratio-counter always shows a zero-flatline. Other counters like avg. disk queue length do work.It works correctly when I open the performance monitor itself and add the buffer cache hit ratio-counter.Quite annoying...I got to select my perf counters each time again and again when I'm starting a new RDP session. Any clues on this issue?Some system specifications:* Windows Server 2008 R2 Enterprise SP1 * SQL Server 2008 (10.0.2766) Thanks,Jack

Cursor

Dreamsz — Thu, 27 Dec 2007 12:15:55 GMT

i have a job which is failing my lead wants me to Remove the cursors from the job and modify it any suggestion would be of great help please--DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT database_name FROM WHERE status='y'AND database_name NOT IN ('master','model','msdb','tempdb') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd)FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursorthanks

Execution plan not showing completely

guthey.aditya — Wed, 02 Nov 2011 16:47:01 GMT

I am trying to debug a stored procedure and I am having issues looking at the complete execution plan. It shows the execution plan to a certain extent and then it greys out as shown in the attachment. Did anyone face a similar issue before and what is the fix? I tried to set several options within management studio with no luck. Thanks a lot in advance.

Finding index Fragment details in SQL2000

ravisamigo — Wed, 02 Nov 2011 02:43:22 GMT

hi,Please advice me the query to find the index fragment details in SQLServer 2000.Thanks and Regards,Ravi.

Necessity of having clustered index in huge tables

Knowledge Hunter — Mon, 24 Oct 2011 22:39:59 GMT

Hi All,We have a fact table containing 28489211 rows and 18997272 KB.We have created a few non clustered indexes based on the queries those are regularly used.But there is not much of unique values in any of the tables.In spite of combining 3-4 columns we are getting around 270 repetitive rows for some combination.In this scenario is it a wise decision to go for clustered index.Does it have any adverse effect on performance of having such a huge table in Heap?

Problem with sp_cursorfetch performance

John Farnell — Wed, 21 Sep 2005 23:43:00 GMT

I am running a query on SQL Server 2000 Service Pack 3 in Query Analyser. It's performance in this environment is fine - a matter of millisecs. However when I request the very same SQL from within my client application the performance is terrible. I then altered the method by which I connect to the db from the client and the performance returned to the original level experienced in Query Analyser. So I had a situation where I can make the same SQL request from the same client perform either badly or well depending on a connection setting

I then decided to run SQL Profiler against both the slow and fast options to try and isolate any differences. The profiler suggests things are exactly the same, except, in the slow option, an sp_cursorfetch is being called while an sp_executesql is being called in the fast option. In both case the same 59 rows are returned.

Further to that, the profiler tells me that the sp_executesql performs 5318 reads and has cpu time of 485 m/secs, while the sp_cursorfetch with the very same sql performs 2.9 million reads and has a cpu time of 20750 m/secs. Is the execution plan different from one case to another?

Can anybody explain to me what issues would make the very same query run badly via sp_cursorfetch but still be lightening fast via sp_executesql and also what I might try to improve the poor performance?

Thanks

John

[SQL Server 2003] [Urgent] Performance issue

sd 31430 — Thu, 13 Oct 2011 07:40:01 GMT

Hello,We are using SQL Server 2003 in our office.And yes, it is clear to me, that there are newer SQL-Server VersionHowever...Unfortunately, typical DQL SQL-Statements run very slowly.SQL Statements are executed, though you could get a coffee in the meantime.As an example, a typical table contains about 600.000 rowsI asked google and Voila....Here I am.Is there a way, to increase the performance of the entire database with dbcc e.g. shrink tempdb or similiar ?What about compressing the work tables ?The hardware can not be changed, at the moment.Thank you, in advance...Hopefully,Stephan

Buffering 83 mgs per second

jmoog — Tue, 20 Sep 2011 22:14:47 GMT

Hi,I am a newbie in SQL, we had a project that inserts more than 83 msgs per second in the table. I want to ask if we're doing the process in the best way.Current process: We had a table Temp A/B which receives messages. If messages inserts in TempA process of summarizing will do in TempB and vice versa. The estimate DB size is 1 GB/day. Do you think switching or buffering of table TempA/B is the best way for this process? If not please recommend other process.thanks

How to convert varchar into datetime

eko indriyawan — Sat, 15 Oct 2005 01:50:00 GMT

hallo..........

i have a problem like this..........

i have a table, we call it as ConvertTable

and there are several filed in the ConvertTable ; Date | Time | Message

Datatype :

Date -->> Varchar(25)

Time -->> varchar(25)

Message -->> varchar(500)

i want to show data from ConvertTable that datattype from field Date as datetime

i just try with this query :

SELECT cast(Date as Datetime) as Date from ConvertTable

when i run, i get error message like this :

[Microsotf][ODBC SQL Server Driver][SQL Server]The Convertion of char data type to a datetime type resulted in an out-of-range datetime value.

i confused with this condition.

btw, anyperson who can help me in solving this problem ????

thanxs...

Bulky non clustered index

Knowledge Hunter — Tue, 23 Aug 2011 06:51:32 GMT

Hi All,We have a data warehouse where tables are joined on multiple criteria.Non clustered indexes were created with a combination of as many as 10 columns (all 10 columns are used in where clause).The table does not have a clustered index.Please let me know whether it is effective to have such huge non clustered index?Will a usage of clustered index in this case going to be effective?

remove read only from a SQL Server data base

med.b_boss — Fri, 26 Aug 2011 09:46:57 GMT

hyi have a data base sql Server .I wrote a stored procedure or attach my database but it is attached in read only mode how can remove read-only. thank's for help ^^this is my stored procedure.create procedure attacheasdeclare @trouvemdf intdeclare @trouveldf intif exists (select name from sysdatabases where name='Gestion_Parc')print'deja attache'else beginexec xp_fileexist 'C:\Gestion_Parc.mdf',@trouvemdf outputexec xp_fileexist 'C:\Gestion_Parc_log.ldf',@trouveldf outputif @trouvemdf=1 and @trouveldf=1 beginexec sp_attach_db 'Gestion_Parc','C:\Gestion_Parc.mdf','C:\Gestion_Parc_log.ldf'endelsebeginprint'file introuvable'end

Query debuging

Beginner_2008 — Wed, 24 Aug 2011 14:42:01 GMT

Can anybody give me the reasons why not to use cursors in detail...Thanks..

UPDATE STATS WITH FULLSCAN - best way to implement?

Marios Philippopoulos — Tue, 08 Apr 2008 12:25:51 GMT

I just came off a phone call with Microsoft. We had a severe performance issue with our SQL 2000 database. After running "UPDATE STATISTICS tblName WITH FULLSCAN" on a large table (10,000,000 rows), the problem was fixed!The (new) thing I learned today is that, although our database stats was up-to-date, the sampling for the update was inadequate. Running the following command on the indexes of the large table, gave a sampling percentage of only 1% of all rows:[quote]DBCC SHOW_STATISTICS (tblName, idxName);output:-------rows: 10,000,000rows sampled: 100,000[/quote]Given what I now know, I would like to implement a procedure of updating the stats of all tables in the database WITH FULLSCAN on a rotational basis.Anyone have any scripts/advice on doing this?

Asynchronous stored procedure calls

BradleyB — Thu, 03 Apr 2003 08:43:00 GMT

Can an asynchronous stored procedure call be made from another stored procedure? Currently I make a set of inserts into a table. I then roll up the information that was inserted, and pass it on to a parent table (that is handled in the procedure I want to call async.) The information is not critical, but useful to the user. It also makes access time faster.

Performance Analysis of Logs Tool for SQL Server 2000

thunderousity — Fri, 15 Jul 2011 04:05:00 GMT

I have recently downloaded version 2 of the PAL tool to analyse various counters.I can happily run this on SQL Server 2005/8However, there is no option for 2000!? Earlier versions of PAL don't seem to be available either.Can I still run this against 2000I'm aware that there are less counters in 2000, however are the; 1. thresholds in PAL the same as 2005/82. different counters/same counters but behave differently in 2000 to 05/08.I'm a newbie and i'm using the PAL tool to learn about the counters and what they mean, whilst trying to gauge the performance of a problem 2000 server.

sp_execute is consuming high resource

Knowledge Hunter — Wed, 29 Jun 2011 23:18:56 GMT

Hello All,I found that sp_execute is consuming high resorces in my database.the commands are as follows:sp_execute 1sp_execute 8sp_execute 6Can anybody please let me know how can a find what stored procedure is it running?I have checked the sp_prepare script preceding sp_execute command from SQL Profiler data. But the SQL script captured is incomplete.From parts of the code I can make out a select statement that it is running a lot of aggregate function and string concatanation.I have checked the followng link but is not of much help:http://www.sqlservercentral.com/Forums/Topic701255-65-1.aspxCan anybody help me to find out what is the query that is being executed and how can I reduce the time taken to execute sp_execute command?I will have to fix the code that is causing sp_execute ti run so long.So tips to fix the code is appreciated.

Weird timing with Audit Logout Event

tedwards — Thu, 08 Jul 2010 14:43:44 GMT

We are experiencing an issue involving a stored procedure run multiple times on 2005 on two different servers. Some Background: Both servers are running 2005, sane service packs, os etc. Both machines are identical, one is used for UAT one PROD. The UAT server is smaller (12 CPU, 64 GB RAM), PROD is (24 CPU, 96GB RAM). The issue we are seeing that a certain transaction consisting of approx 150 procedure calls operates approximate 8 seconds faster on the smaller server than the Production server. The transaction is run from an ASP.NET connection from a Web server separated by only a firewall and two ethernet cables. The Connection is using MS Enterprise library to handle connecting and disconnecting. The ASP.NET code, stored procedures, tables, indexes, stats are all the same. When the transaction is run, there is point where the same procedure is called 80 times in a row. Each time the procedure is executed, the connection connects, executes the procedure, then disconnects (I will list profiler stuff in a minute). What we are noticing is that on the production server, a single procedure call takes ~100 ms from login to logout. The same sequence on the smaller UAT server takes 10-15 ms. SO the difference of the 80-90 ms on each call, being called 80 times, is effectively the difference in the two queries, we have compared the traces from end to end and they are almost identical exempt for this sequence. I will show some profiler entries in next post.

Auto Update Statistics is On

Ayie — Tue, 04 Nov 2008 20:24:29 GMT

Hello DBAAnyone knows how to:1. Logging detect auto update statistics2. any queries can capture the data, if the auto update statistics is running3. I have run profiler but this one cannot see any object name, what is updated.because i dont want to used manual update. i need to know how to capture the auto update statistics that is on in sql 2000Previously a run job every other day for sp_updatestats but this one is cause of slowness in my production database they changed the execution plan of the indexes and table. I stopped the job, now is back to normal.Now my worry is how will I know if the auto update statistics is running, and anyone have a query to capture this thing. tested already in profiley but not accurate to get info.DBA Anyone can help me.ThanksAyie

High CPU, Low Reads

yasubmj — Thu, 16 Jun 2011 23:58:03 GMT

Hi,I recently worked on tuning a stored procedure for performance. Here are the perf details of the sproc before tuning:--CPU 125, Reads 23936, Writes 1, Duration 1656After tuning:--CPU 140, Reads 6229, Writes 7, Duration 636Its strange that the reads have come down and so has the duration but the CPU has gone up.Both sprocs were run within a minute of each other and have been consistently showing such a difference. Can someone please guide me on this?PS: In the tuned sproc i have localised the parameters to disable sniffing.Regards - Yasub

Performance slow down after stored proc called many times in loop

aracy — Wed, 15 Jun 2011 09:52:51 GMT

Hi everyone. I'm new to this forum so I hope I'm posting this in the correct place.I have a stored proc that is called many times in a loop and selects chunks of data as it goes until it select all records in the table. Here is the query that gets run in my stored proc via dynamic sqlFirst time calledWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 1 and 200000Second time called.With RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 200001 and 400000Etc.Once this stored proc gets called enough times so that it's processing rownums in the 6800000, it slows down like crazy.First runs, you can see the time it takes here.With RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 1 and 200000200000Jun 15 2011 9:30AMWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 200001 and 400000400000Jun 15 2011 9:30AMWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 400001 and 600000600000Jun 15 2011 9:30AMWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 600001 and 800000800000Jun 15 2011 9:30AMBy the time it hits around 6800000 and > the time it takes looks like this.Jun 15 2011 9:42AMWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 7400001 and 76000007600000Jun 15 2011 9:44AMWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 7600001 and 78000007800000Jun 15 2011 9:45AMWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 7800001 and 80000008000000Jun 15 2011 9:46AMWith RowTable As (Select row_number() over ( ORDER BY UF_EVENT_DATA.UF_EVENT_DATA_ID ) As RowNum, * FROM PHDAPP.UF_EVENT_DATA ) SELECT * FROM RowTable Where RowNum between 8000001 and 82000008200000It just gets slower, and slower and slower.I've tried to find out what causing this and I came across something called Parameter Sniffing. Sooo, it tried this in my stored proc. Didn't helpALTER PROCEDURE [mydb].[MYPROC] ( @SelectList nvarchar(1000), -- this is a required parameter @WhereCondition nvarchar(max) = NULL, @WhereParams nvarchar(500) = NULL, @JoinTables nvarchar(max) = NULL, @OrderByExpression nvarchar(250) = NULL, @ReturnInPages bit = 0, @StartRowCount nvarchar(10) = 0, @EndRowCount nvarchar(10) = 50, @debug bit=0, @return_code int=0 output )with execute as 'myuser'AsBEGIN SET NOCOUNT ON--dbcc freeproccache DECLARE @SQL nvarchar(max), @DynSql nvarchar(max), @OrderBy nvarchar(200), @TblName nvarchar(40), @myStartRowCount nvarchar(10)=0, @myEndRowCount nvarchar(10) = 50, @myReturnInPages bit = 0, @mydebug bit = 0, @MySelectList nvarchar(1000), -- this is a required parameter @MyWhereCondition nvarchar(max) = NULL, @MyWhereParams nvarchar(500) = NULL, @MyJoinTables nvarchar(max) = NULL, @MyOrderByExpression nvarchar(250) = NULL, @Optimize nvarchar(500)=NULL, @Myreturn_code int=0 set @myStartRowCount=@StartRowCount set @myEndRowCount=@EndRowCount set @myReturnInPages=@ReturnInPages set @mydebug=@debug set @MySelectList=@SelectList -- this is a required parameter set @MyWhereCondition = @WhereCondition set @MyWhereParams =@WhereParams set @MyJoinTables =@JoinTables set @MyOrderByExpression =@OrderByExpression set @Myreturn_code =@return_code etc.Does any know what I can do to get this to preform. I'm pretty much out of ideas. I don't know how to force it to expire and re create the Exec Plan for this so I don't know if that would help but from what I read, the code above should have done this.Thanks,Amber.

PErformance difference with Management Studio

mishka-723908 — Thu, 09 Jun 2011 09:50:14 GMT

Hello, I have two different work stations with management 2008 studio installed. Both are connecting to the same SQL DB server.Running the same query from each of the WorkStations gives very different performance results. One work station can take 30-seconds and in the other it takes up to 30 minutes. This is for exactly the same query.Anyone have any ideas?Thank you,Michael

SQL Performance Monitoring using Quest Spotlight/Foglight Advanced ???

TX_DBA — Fri, 03 Jun 2011 10:20:14 GMT

[i][font="Verdana"][size="3"]Hi all,I need useful information with links to refer regarding "SQL Server Performance Monitoring" using [b]Quest Spotlight[/b] & [b]Quest Foglight advanced.[/b]i would also like to know the things that Quest tools can't perform while compared to SQL Profiler.Appreciate your response & Thanks in advance[/size][/font][/i] :-)