Same Query Same Database Different Servers
-
search for my posts, i posted a bunch here a month or two ago. Or you can email me and i can send them
-
SQL Noob, not that I didn't want your scripts (I'm not one to reinvent the wheel everytime I need one), but I thought it best to write my own as a learning tool for the new features of SQL Server 2005. Best way to learn them is to use them.
Thanks for the offer!
-
You said in your initial post that the dev execution plan was more efficient than the production plan - how so exactly?
-
Hard to say anymore. Having created new indexes and statistics, I don't have the origianl plan available to compare it to. All I can say is that based on the plan the query generated in DEV, it returned results in half the time the same query did in PROD where it had a different query plan. If I remember correctly, the plan in PROD had a sort at the end, the one in DEV didn't,; and further up the plan PROD was using a nested loop where DEV was using a hash join.
Beyond that, I can't remember much.
-
Are the dev and prod databases similar in size and data? Did your prod query have an order by clause that you didn't include on the dev query?
Brian
-
Assuming both DBs are identical in terms of objects (especially indexes) and similar in terms of data, you may want to run the comparison again ... assuming it still shows a difference, at least the plan will help you identify which tables and actions are being treated differently and you can narrow down your search accordingly.
Matt.
-
Unfortunately, we also had other issues to deal with as a result of the upgrade from SQL Server 2000 to SQL Server 2005 besides this particular query running slower on SQL Server 2005 from SQL Server 2000. We had basic application functions start timing out as well.
Prior to running a trace and using DTA to help tune the database, I simply used sys.dm_db_missing_index_details and created 140 new indexes in production (quick and dirty) and that helped the immediate problem as upper IT management was starting to push for moving BACK to SQL Server 2000 even though the PeopleSoft HR upgrade requires us to move to SQL Server 2005.
After running the DTA, I have dropped the 140 indexes I created and implemented the 35 indexes DTA recommended be created. Now that we have dealt with that issue, I am now able to look at this query again. I have two nearly identical databases (TRN and PRD0 and the query runs faster in TRN than in PRD and the execution plans are different.
When I say nearly identical, PRD as current information where TRN is a week behind data wise.
I need to figure out how to get the plans in a text format and post them both and see what others think is going on. It doesn't make any sense to me.
-
Regarding our issues, we finally broke down and called Microsoft for help. While we had a tech on the line we found one thing we had not looked at or for. We had Diskkeeper installed (Microsoft asked us to remove it) and it appears it was trying to run during the day when users were experience severe performance issues. In addition, we have upgraded to SP 2 (when initially installing SQL 2005 we only installed SP 1 as that was the last we heard was supported by PeopleSoft/Oracle), reran (again) an UPDATE STATISTICS with FULLSCAN on all tables, and put the /3GB switch in the boot.ini file with the /PAE switch.
We'll see how things go this week and I'll let you all know as well.
I appreciate your assistance and ideas as we struggled to figure out what was going on.
As for the same query same database different server; looks like we just need to find the right set of indexes to improve performance in production and not cause other performance issues.
Again, thanks!
-
did you have good luck with DTA? i've only used it a few times, but found it completely useless in each case and getting dirty with testing much more useful
-
Actually, the DTA's recommendations did help general performance of the system. It recommended 36 new indexes that when implemented were beneficial to overall performance. We still had issues at times where users enteing PO's would get timed out, but with the assistance of both Microsoft (finding we had a 3rd party software product possibly misbehaving during working hours) and our PeopleSoft developers we found 2 queries (so far) that had a significant impact on users sving PO's. Using the execution plan to find missing indexes just allowed me to had two indexes that may finally put this issue to bed (I hope!).
The only thing I am still unsure of is the 95 new statistics it recommended. I think you may have read the other thread I started on that subject, but I am not sure if those helped or hindered the system. I don't know or understand statistics enough to really know.
I think the best way to use DTA, is to have it make recommendations, and then, as you say, get dirty testing them. I didn't really have the luxury of testing much due to having higher ups wanting things done yesterday, show I just modified them slightly (dropped all INCLUDE columns, as we aren't sure if the PeopleTools will support those at this time), and adding all the recommendations from the DTA.
Hopefully, we will have a little more time for testing our HR system before we go live with 9.0 (which started all this any way since it force a move to SQL Server 2005!).
-
I recently tried the DTA for the first time on SQL2005. For one query it identified 13 new indexes. I tried implementing one at a time to see how improvement was. Out of the 13, only one made a measurable difference. Rolled the other 12 out and was good to go.
Incidentally, the one that worked happened to also be the one I recommended myself prior to running the DTA. Guess I might have learned a few things about indexing the last 12 years
As far as statistics I let SQL deal with that as much as possible autmotically.
-
Once we are comfortable with the system performance, I hope to be able to start rolling some out. We were in a crunch to improve system performance, so had to use the shotgun approach. Sometimes, you gotta do what you gotta do, and clean up afterward.
-
oh absolutely. I was not in a (major) crisis as I was speeding up a weekly process that had just taken way too long to finnish.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply