We had a SQL 2005 server running for XML EXPLICIT queries quite happily with no performance issues. The machine (a Windows 2003 server) has unfortunately died so I've had to do an emergency provision of a Windows 2012 box. The databases files have been reattached to a 2008r2 and "work". However the queries are horrendously slow. 5 seconds per query when previously they were in the .x times. This makes the websites that they power unusable. I've rebuilt all the indexes and I've run DBCC FREEPROCCACHE on all machines but this has had no noticable effect. What else can I look at ? I can't run them on the 2016 SQL instance on the box because some of the queries use non-ANSI *= joins (I said it was old!).
Poor SQL performance after server transfer
220 Views Asked by Andy Macnaughton-Jones At
1
There are 1 best solutions below
Related Questions in SQL
- Can MVC.NET prevent SQL-injection at razor or controller level?
- SQL server not returning all rows
- When dealing with databases, does adding a different table when we can use a simple hash a good thing?
- Creating a parametrized field name for a SELECT clause
- Combine two rows based on common ID
- Column displays each count
- Slick query for one to optional one (zero or one) relationship
- Aggregate and count in PostgreSQL
- MAX and GROUP BY - SQL
- SQL statement for a tricky 2 table query
- How to create nested selects with sql?
- Pull and push data from and into sql databases using Excel VBA without pasting the data in Excel sheets
- Best Practice for adding columns to a Table in Oracle database
- SQL FIFO STACK using two tables
- SQL Query - Order by String (which contains number and chars)
Related Questions in SQL-SERVER
- SQL server not returning all rows
- Big data with spatial queries/indexing
- Conditional null constraint on Null
- SQL Query - Order by String (which contains number and chars)
- Optimising a slow running SQL Server Stored procedure ordered by calculated fields to return a closest match
- Dynamics CRM Publishing Customizations - Multi Developers
- Is there anyway to set the relationship of many tables from Model?
- Implementation of Rank and Dense Rank in MySQL
- ORM Code First versa Database First in Production
- MVC : Insert data to two tables
- Data streams in case of Merge
- table with multiple IDs but seperate notes need sorting (Tried SQL code to make a union query)
- SQL table Partitioning by Year with ColumnStore index implemented on the table
- Defining which network to use for SQL Server 2012 Management Studio
- Fill a week days in a table with preceding Sundays value
Related Questions in DATABASE-PERFORMANCE
- Big data with spatial queries/indexing
- Do I have to drop the primary key or reload the table data for partitioning in MySQL InnoDB?
- SQL Server Query Performance with a lot of data
- Best practices of commit() when inserting many rows
- Optimize mysql query that take long execution time about 4 minutes
- Compare multi values against multi values, maintaining performance
- DB Index not being called
- SQL Server Indexes for multiple searchable fields
- Scaling MySQL database, increasing performance to many connections
- Reducing database calls
- Fastest way to delete millions of registers in sql server
- SQL Server job failed due to space issues
- Read or update quickly hundred millions of relations on Solr
- Exact same query and database. In production more than 100x slower
- Database bottleneck identification using jmeter
Related Questions in FOR-XML-EXPLICIT
- XML query tags comes stacked instead of nested
- Why is the CDATA directive not working this for xml explicit query?
- Poor SQL performance after server transfer
- Duplicate values FOR XML EXPLICIT
- FOR XML EXPLICIT Parent ID not among open tags
- Different values returned by SQL Server 2000 and SQL Server 2014 when returning results using for xml explicit
- SQL Server FOR XML explicit error in generating same name nodes
- how to generate xml with element and attribute using xml explicit
- SQL Server EXPLICIT mode with FOR XML
- Remove xmlns="" name space declaration when using FOR XML EXPLICIT
- Suggestions on Troubleshooting "FOR XML EXPLICIT" Msg 6833 "requires parent tags to be opened first" Error
- SQL Server 2019 FOR XML nested nodes preserving CDATA
- For XML Explicit Nesting Data Incorrectly
- Specify XMLNAMESPACE in SQL Server Query with For Xml Clause, Explicit mode
- ORDER BY Clause in FOR XML EXPLICIT
Trending Questions
- UIImageView Frame Doesn't Reflect Constraints
- Is it possible to use adb commands to click on a view by finding its ID?
- How to create a new web character symbol recognizable by html/javascript?
- Why isn't my CSS3 animation smooth in Google Chrome (but very smooth on other browsers)?
- Heap Gives Page Fault
- Connect ffmpeg to Visual Studio 2008
- Both Object- and ValueAnimator jumps when Duration is set above API LvL 24
- How to avoid default initialization of objects in std::vector?
- second argument of the command line arguments in a format other than char** argv or char* argv[]
- How to improve efficiency of algorithm which generates next lexicographic permutation?
- Navigating to the another actvity app getting crash in android
- How to read the particular message format in android and store in sqlite database?
- Resetting inventory status after order is cancelled
- Efficiently compute powers of X in SSE/AVX
- Insert into an external database using ajax and php : POST 500 (Internal Server Error)
Popular Questions
- How do I undo the most recent local commits in Git?
- How can I remove a specific item from an array in JavaScript?
- How do I delete a Git branch locally and remotely?
- Find all files containing a specific text (string) on Linux?
- How do I revert a Git repository to a previous commit?
- How do I create an HTML button that acts like a link?
- How do I check out a remote Git branch?
- How do I force "git pull" to overwrite local files?
- How do I list all files of a directory?
- How to check whether a string contains a substring in JavaScript?
- How do I redirect to another webpage?
- How can I iterate over rows in a Pandas DataFrame?
- How do I convert a String to an int in Java?
- Does Python have a string 'contains' substring method?
- How do I check if a string contains a specific word?
If your query was running fine before, consider what else have changed - the query planner and actual execution plan might help to pinpoint this.
When you say you are joining, have you considered how much you join? If the new machine have more data in the database, then a join might quickly become prohibitively expensive. This can be done by reducing the data you need, as less datahandling means less workload.
Is there something you can pre-calculate before you run your query, or otherwise change to make it run faster?
I assume you do a SELECT, but if you UPDATE or DELETE data, the indexes also need to be recalculated, which takes a long time (in this case, disable the index, insert all the needed data and then recalculate the index)
You don't mention any XML handling, but have marked the for-xml tag. If your join is performed on XML data, using Xquery to get the data might also give a boost to performance.