InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:15:15 GMT20http://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxThanks for your continued help Roddy.just a FYI, updated to the lastest CU3 for 2008 r2 sp2, still no improvement. I will review your last post and come back to you on possible findings. We have also opened a case with MS. Hoping this might get us some traction or confirmation that SQL 2008 does handle something differently.Cheers,BradSun, 18 Nov 2012 18:11:05 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxHi Brad, I'm in Brisbane. Still scratching my head on this, coffee doesn't seem to have helped. I think various things are red herrings though. As you have pointed out through out the post the schema and indexes have not changed and you have rebuilt indexes, stats, etc.The original query is a serial one so MAXDOP is irrelevant. I'm not convinced in any way about tempdb being the issue. If you had an io issue there, it would make the query slow but it would not cause the query plan to change completely during optimsation where the optimiser does not know about any i/o issue. You could prove this by removing the actual insert and just run the select. If it is still slow it's not a tempdb issue. Also "set statistics on" and see what they say. Likewise in fact for my theory about the bug, it wouldn't affect the optimiser. Our plans stayed the same, they just ran slow.However I notice on the extreme right of the original plan where it joins TaxonContentLink to Article there is a "constant scan" which creates 3 expressions (expr1150,expr1151,expr1149). This looks to me like it relates to the functions which are called (funcTaxonListTagsForWidget,ect). This constant scan does not appear in the slow query so it looks like the functions are being handled differently. Try commenting them out to see what happens. If it runs fine then the issue lies in the functions not optimising in the same way on the new server. Just a few observations to try and eliminate red herrings and maybe try and narrow it down to a specific table or line of code, which maybe 2008R2 optimises differently. CheersRoddySun, 18 Nov 2012 17:01:34 GMTRoddy.CAMERONhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxYeah I am in AU, SYD - its only 4:00PM here.CheersThu, 15 Nov 2012 21:52:52 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxWow, you're up early. At least it's 14:30 in the afternoon in Oz. May well not be the issue but good to eliminate it. Just removing the nolocks should prove it one way or the other.CheersRoddyThu, 15 Nov 2012 21:42:00 GMTRoddy.CAMERONhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxHi Roddy,thats helpful. While the CPU usage is low on the query it does jump up a little. If this server were loaded it actually might have a serious impact. I am going to look into this a little more.Thanks.BradThu, 15 Nov 2012 21:37:52 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxHi BradSomething to check. I notice all your joins use 'nolock'. There is a bug in SP2 which causes high cpu usage on queries that use nolock against a table with a blob, but the cpu usage is not visible against the query. We've just spent a painful time hunting it down. It's fixed in CU3. Just thought I'd mention it as we saw queries running inexplicable slowly when we applied SP2 and you seem to be selecting from tables with images.CheersRoddyThu, 15 Nov 2012 21:25:39 GMTRoddy.CAMERONhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxI can't tell from the query plans or your posts if your temp table has a primary key. If not, create a primary key when declaring the table and perhaps that will make a difference. If you do have a primary key, then just chalk up this post as my 2 cents. FWIW. :-) Wed, 14 Nov 2012 12:48:17 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspx[quote][b]carsten.kunz 37074 (11/13/2012)[/b][hr][quote][b]opc.three (11/13/2012)[/b][hr][quote][b]carsten.kunz 37074 (11/13/2012)[/b][hr]For the SQL Server I also usually set max degree of parallelism to 1, under advanced server settings.[/quote]Why do you do such a thing?[/quote]I have mostly OLTP systems, not much data warehousing or reporting. It gives me more consistent query execution times and plans, and so far there was really no need for this.[/quote]If that has been your experience on the instances you manage then that is fair enough. I asked because taken out of context your comment "usually setting Max Degree of Parallelism to 1", if thought of as a general rule of how you would recommend setting up [i]any[/i] new instance, could be seen as starting off on the wrong foot in many cases. Thank you for posting back.Another option to consider, in cases where you have databases with hybrid workloads consisting mostly of queries that will not benefit from parallelism but also containing some more complicated queries that could, is to either leave Max Degree of Parallelism at 0 or set it to a value greater than 1 but less than the number of logical CPUs, and in addition to one of those two changes also raising the [u][url=http://msdn.microsoft.com/en-us/library/ms188603(v=sql.90).aspx]Cost Threshold for Parallelism[/url][/u].Wed, 14 Nov 2012 00:05:27 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspx[quote][b]opc.three (11/13/2012)[/b][hr][quote][b]carsten.kunz 37074 (11/13/2012)[/b][hr]For the SQL Server I also usually set max degree of parallelism to 1, under advanced server settings.[/quote]Why do you do such a thing?[/quote]I have mostly OLTP systems, not much data warehousing or reporting. It gives me more consistent query execution times and plans, and so far there was really no need for this.Tue, 13 Nov 2012 16:28:34 GMTcarsten.kunz 37074http://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspx[quote][b]carsten.kunz 37074 (11/13/2012)[/b][hr]For the SQL Server I also usually set max degree of parallelism to 1, under advanced server settings.[/quote]Why do you do such a thing?Tue, 13 Nov 2012 06:34:18 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxHow many cores do you have. It should be enough to have one or two tempdb data files on the data drives, and one tempdb log on the log drives.Disable auto growth on the tempdb data files and expand them to equal size.For the SQL Server I also usually set max degree of parallelism to 1, under advanced server settings.Do you have any joins in your query which have clustered or covered indexes? Can you align the index fields to have the same sequence as the fields you use for the joins?Example: SELECT * FROM table1 LEFT JOIN table2 ON table1.a = table2.a AND table1.b = table2.bIndex: fields a,bI had queries running in seconds on SQL 2005 which then took many minutes on SQL2008R2:crying:, just because the query and index was not aligned.Tue, 13 Nov 2012 00:29:39 GMTcarsten.kunz 37074http://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxI now have 8 of them spread over the log and data drives at the moment.They were all on the raid 1 OS before, with this configuration I also saw the same sort of issues hence my desperation in spreading them around.Mon, 12 Nov 2012 13:33:19 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxWhere are your TempDb files located?Mon, 12 Nov 2012 05:36:51 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxOK run on each database 1. for the SQL 20052. for SQL 2008 r2Mon, 12 Nov 2012 03:51:31 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxCan you run this on both servers[code="sql"]SELECT OBJECT_NAME(s.object_id) AS TableName, s.name AS StatsName, s.auto_created, s.user_created, STATS_DATE(s.object_id, s.stats_id) AS StatsDate, c.name AS ColumnNameFROM sys.stats sINNER JOIN sys.stats_columns sc ON s.object_id = sc.object_idINNER JOIN sys.columns c ON s.object_id = c.object_id AND sc.column_id = c.column_idORDER BY s.object_id, s.name, c.column_id[/code]Mon, 12 Nov 2012 03:19:36 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxArtoo22 to answer you, the database have the same indexes. Too prove this to myself today (at a loss with this one), I again backed the database up only 3 hours ago re-restored the database, and rebuilt all index's again.I see what your saying, But I just don't have a logical explanation to it. They are 100% like for like in terms of data, schema and structure.Mon, 12 Nov 2012 03:00:16 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxThere should be no contention at all this database server has nothing I mean nothing running on it. The only database is the one that I am providing the details for and its also got no hits, only the ones that I generate on it. However running:[code="vb"]SELECT dm_ws.wait_duration_ms,dm_ws.wait_type,dm_es.status,dm_t.TEXT,dm_qp.query_plan,dm_ws.session_ID,dm_es.cpu_time,dm_es.memory_usage,dm_es.logical_reads,dm_es.total_elapsed_time,dm_es.program_name,DB_NAME(dm_r.database_id) DatabaseName,-- Optional columnsdm_ws.blocking_session_id,dm_r.wait_resource,dm_es.login_name,dm_r.command,dm_r.last_wait_typeFROM sys.dm_os_waiting_tasks dm_wsINNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_idINNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_idCROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_tCROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qpWHERE dm_es.is_user_process = 1GO[/code]renders nothing to view.I have run the update full after the restore, but there is no harm doing that again... So this has been done using maintenance plans doing the following:Checking DB integrity Reorg to all tables and viewsrebuild - sort results in tempDB also selectedupdate stats - fullAfter completion with no faults re-running the query and checking the execution plan, well no joy, its still the same unfortunately, no change from the last posted plan.Mon, 12 Nov 2012 02:53:27 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxYour slow query has a clustered index scan on UploadedImage sending 15,152 rows to a merge join. The fast query does a seek, sending 351 rows to a nested loop join.Do you have the same indexes on this table? Are stats up to date?Mon, 12 Nov 2012 02:53:09 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxmy 2 cents...Estimated and actual rows are significantly different all over the place. I'd re-run an update statistics with full scan.Estimated IO costs seem significantly higher for TempDB. Have you looked at if there's any contention there - sys.dm_os_waiting_tasks?Mon, 12 Nov 2012 02:33:55 GMTadb2303http://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxSure here we go 2 files 1. 2005 - fast.sqlplan - this the old 2005 server2. 2008r2 - Slow.sqlplan - This is the new server, now with TempDB the auto create and update stats now on.Mon, 12 Nov 2012 02:21:04 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxI wouldn't recommend them to off on tempdb, at least not the Auto Create. Can you switch them on and try your query again. Please attach the new query plan.Mon, 12 Nov 2012 01:56:58 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxBoth are set to false or 0. My SQL 2005 has both set to true. Though these settings were also tried and tested before changing them (changing them was a recommendation), It still provided the same outcome with the cost on SLQ2008r2Mon, 12 Nov 2012 01:52:39 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxI can see many warnings with your temp tables and column statistics. Is TempDb configured to auto create statistics?[code="sql"]SELECT name, is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_onFROM sys.databasesWHERE database_id = 2[/code]Mon, 12 Nov 2012 01:46:02 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxThanks Jeff, I hope so, this one is causing me a few sleepless nights!Mon, 12 Nov 2012 00:55:02 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxI have to admit... I've never seen anything like this before. Hopefully, the resident expert at such things will show up for this one.Sun, 11 Nov 2012 23:33:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxThanks Jeff,I have zipped both plans up.1. Long running Plan.sqlplan - this is the one that is taking longer SQL2008R2 (problematic)2. Quick plan.sqlplan - This is the 2005 box that is rather quick to execute.Sun, 11 Nov 2012 15:51:36 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxEstimated and even supposed actual cost suck for these types of estimates. The real problem is likely futher to the right in the execution plan. I can see that the first one has nested loops and that the second has a merge instead.My recommendation would be to read the article at the second link in my signature line below and post the actual execution plans using the methods from that article.It may actually turn out to be the insert into the temp table that cause the problem if you're running on a new SAN. It seems there's been a rash of "lemons" hit the streets from a couple of previously trusted vendors, lately. I don't want to mention the name because I don't want to get sued for saying their products have really taken a turn for the worse.Sun, 11 Nov 2012 15:33:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxSorry for the late response guys Yes I have repeated the execution of the query more than 10+ times with the same % on the new database server.in terms of the migration to the new server, yes it was a simple backup and restore, I have rebuilt the index several times and also rebuilt the full text index's not that they come into the picture here... Just not getting anywhere with this.Sun, 11 Nov 2012 13:41:09 GMTBrad Marshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxWhat post-migration steps have you carried out on the new server?I am guessing you backed up and restored the 2005 database onto the 2008 server?Did you run DBCC UPDATEUSAGE, rebuild and update all statistics?Fri, 09 Nov 2012 03:56:50 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxHave you tried to repeat same test in new server? see and paste the result hereFri, 09 Nov 2012 03:49:44 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1382797-360-1.aspxHi Guys, I have an issue that is completely baffling I need a guru's guidance / help.so a little bit of an over view first.We currently have a 2005 sp3 server in place at the moment its serving a number of databases, its spec is around 24 gig of RAM, 2 2.6 quad core CPU's and its disk are in a raid 5 config.this server is has no SQL optimization its almost a default install.Now we have a new server that is being built it has SQL 2008 r2 Sp2,2 Latest X series processes (they are far better than that in the Sql 2005) and 48 gig of RAM it has 3 disk sets 1 raid 1 for the OS, raid 10 for both the log and data drives... MUCH better overallThis new server is in its testing phases before its to go to production, but I am in a situation where I can put it into production as it's performance seems dismal in comparison to the SQL 2005 box. I need some understanding as to why.I can tell you from I/O testing that the new 2008r2 server from a hardware point of view blitz's the old server.But the odd thing is when hitting a website that is on the same web server, there is a constant 3 second addition when requesting the website.So to confirm that it is 100%the db that is the cause I did a SQL trace on the new server, I found a query that has a significant duration, I then pulled that query and ran it on both 2005 and 20082005 does it a second faster than 2008, If I look at the execution plan I can see that: On an insert to the temp table on 2005 it has a 0% cost to [img]http://www.elcomcms.com/ftp/2005.png[/img]But yet on my SQL2008 box its got a 93% cost.[img]http://www.elcomcms.com/ftp/2008.png[/img]So to me it looks like temp DB is caching this, I need to understand how I can get this same performance if not better from the darn TempDB... I am dying for any help / guidance on this one.Thu, 08 Nov 2012 19:55:16 GMTBrad Marsh