November 1, 2013 at 5:39 am
Hi
I have couple of SQL Tables with customers information the database works fine on SQL server 2005 but when doing a search when the tables are under SQL 2008 R2 SP2 it runs really slow example I type couple of letters in the search field seems to take its time to input what I have typed like really bad lag
anyone got any ideas please.
Thanks
November 1, 2013 at 6:48 am
Mike.brickles (11/1/2013)
HiI have couple of SQL Tables with customers information the database works fine on SQL server 2005 but when doing a search when the tables are under SQL 2008 R2 SP2 it runs really slow example I type couple of letters in the search field seems to take its time to input what I have typed like really bad lag
anyone got any ideas please.
Thanks
Hello and welcome to the forums. You aren't giving us much to go on here. We can't see what's on your screen, so we don't know anything about what you're using or encountering other than what you post. Here's a few questions to get started:
1. What search field are you typing into? Is this an application you've written?
2. How's the performance of the query in in SSMS? That will eliminate any extra variables and narrow the execution down to the database.
3. How did the tables and data get from 2005 to 2008? If the indexes aren't there in 2008, that will cause performance problems.
4. Can you quantify "like really bad lag"?
In order to troubleshoot a query, we'll need some readily-consumable DDL, data, expected results and what you've tried so far. Please see the link in my signature for advice on how to post questions to the forum.
November 1, 2013 at 7:43 am
I moved the tables by using the import and export data
I really dont know much about sql at all
November 1, 2013 at 8:23 am
Mike.brickles (11/1/2013)
I moved the tables by using the import and export dataI really dont know much about sql at all
Are the 2 schemas the same? Indexes, statistics? Have you reindexed the tables? Are statistics up to date?
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
November 1, 2013 at 8:25 am
how do i index the tables ?
November 1, 2013 at 8:27 am
Kurt W. Zimmerman (11/1/2013)
Mike.brickles (11/1/2013)
I moved the tables by using the import and export dataI really dont know much about sql at all
Are the 2 schemas the same? Indexes, statistics? Have you reindexed the tables? Are statistics up to date?
Kurt
That's it. When you use the import/export wizard, the table structure and data are copied, but the keys, constraints and indexes are not. You'll need to update the table on 2008 to match the one in 2008 to be able to compare apples-to-apples.
November 1, 2013 at 8:36 am
Mike.brickles (11/1/2013)
how do i index the tables ?
In 2005, generate the script to create the table by using Script Table As...Create To...New Query Editor window. Then connect to your 2008 database. You already have the table created and populated, so you can't create it unless you drop it first, which would result in removing all the data. So, only run the appropriate commands in the script to create the indexes.
Next time, I would create the table first and get the indexes and constraints created first, then use the import/export wizard to copy your data over to 2008.
November 1, 2013 at 8:53 am
If I'm going to move data from one server to another via Import/Export I will script out the source database first and place it on the destination box. Then when I move the data from source to destination at least I know the 2 schemas are the same. Finally I reindex all of the indexes and update the statistics before querying the destination tables.
Unless the source & destination boxes are exactly the same, it has been my experience that the 2 boxes will not respond the same. There are so many issues that determine performance that I've realized that the 2 will be different. But if there is a difference in minutes or hours then chances are there is something I've might have overlooked.
Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
November 1, 2013 at 9:10 am
Kurt W. Zimmerman (11/1/2013)
Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.
Amen to that. Problems always show up where they should when the test hardware is less then production. Once you have it running well in test, then there won't be any nasty surprises on production. Plus, you get to be pleased with how well your code performs in production. 😀
November 1, 2013 at 9:15 am
Kurt W. Zimmerman (11/1/2013)
If I'm going to move data from one server to another via Import/Export I will script out the source database first and place it on the destination box. Then when I move the data from source to destination at least I know the 2 schemas are the same. Finally I reindex all of the indexes and update the statistics before querying the destination tables.Unless the source & destination boxes are exactly the same, it has been my experience that the 2 boxes will not respond the same. There are so many issues that determine performance that I've realized that the 2 will be different. But if there is a difference in minutes or hours then chances are there is something I've might have overlooked.
Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.
Kurt
Just out of interest..why would you update statistics in this case?
November 1, 2013 at 9:35 am
Hi Thank you for the help
I have managed to sort it I did a fully backup of the database from 2005 and did a restore on to 2008 job done
November 1, 2013 at 9:36 am
thanks
November 1, 2013 at 9:38 am
clayman (11/1/2013)
Kurt W. Zimmerman (11/1/2013)
If I'm going to move data from one server to another via Import/Export I will script out the source database first and place it on the destination box. Then when I move the data from source to destination at least I know the 2 schemas are the same. Finally I reindex all of the indexes and update the statistics before querying the destination tables.Unless the source & destination boxes are exactly the same, it has been my experience that the 2 boxes will not respond the same. There are so many issues that determine performance that I've realized that the 2 will be different. But if there is a difference in minutes or hours then chances are there is something I've might have overlooked.
Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.
Kurt
Just out of interest..why would you update statistics in this case?
Simply to insure that there is no fragmentation in the statistics. Fragmented statistics will cause poor performance.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
November 1, 2013 at 9:55 am
Kurt W. Zimmerman (11/1/2013)
clayman (11/1/2013)
Kurt W. Zimmerman (11/1/2013)
If I'm going to move data from one server to another via Import/Export I will script out the source database first and place it on the destination box. Then when I move the data from source to destination at least I know the 2 schemas are the same. Finally I reindex all of the indexes and update the statistics before querying the destination tables.Unless the source & destination boxes are exactly the same, it has been my experience that the 2 boxes will not respond the same. There are so many issues that determine performance that I've realized that the 2 will be different. But if there is a difference in minutes or hours then chances are there is something I've might have overlooked.
Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.
Kurt
Just out of interest..why would you update statistics in this case?
Simply to insure that there is no fragmentation in the statistics. Fragmented statistics will cause poor performance.
Kurt
No idea what you mean by that.. Did you mean index fragmentation? If so, why would index fragmentation be an issue right after the indexing process? Bear with me, just trying to understand things better.
November 1, 2013 at 10:25 am
clayman (11/1/2013)
Kurt W. Zimmerman (11/1/2013)
clayman (11/1/2013)
Kurt W. Zimmerman (11/1/2013)
If I'm going to move data from one server to another via Import/Export I will script out the source database first and place it on the destination box. Then when I move the data from source to destination at least I know the 2 schemas are the same. Finally I reindex all of the indexes and update the statistics before querying the destination tables.Unless the source & destination boxes are exactly the same, it has been my experience that the 2 boxes will not respond the same. There are so many issues that determine performance that I've realized that the 2 will be different. But if there is a difference in minutes or hours then chances are there is something I've might have overlooked.
Preferably I like to do all of my development on a slower box anyway. This way I can see performance related issues that are not masked by a higher performing box.
Kurt
Just out of interest..why would you update statistics in this case?
Simply to insure that there is no fragmentation in the statistics. Fragmented statistics will cause poor performance.
Kurt
No idea what you mean by that.. Did you mean index fragmentation? If so, why would index fragmentation be an issue right after the indexing process? Bear with me, just trying to understand things better.
Here is a link that can give you a better understanding of Statistics.
http://technet.microsoft.com/en-us/library/ms190397.aspx
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply