troubleshooting Question

SQL Server XML Type Performance

Avatar of DotNetThinker
DotNetThinkerFlag for United States of America asked on
Microsoft SQL Server 2005
9 Comments1 Solution685 ViewsLast Modified:
I work for an insurance company and we store most of our policy information in an xml field in our policy table.
Every night a job is run that creates new renewal quotes and inserts them into our table.
On our front end we allow users to search for renewal quotes by policy number (stored in the xml).
The problem I've run into is that for the first hour or so of the business day the front end is timing out because it takes anywhere from a minute and a half to 4 minutes for the query to run. My thought is that it has something to do with the index on the xml field because after running the query in Management Studio each subsequent query takes about 10 seconds. Could that be caused by the fact that every morning (3am) there are 100-300 records added to the table? It's been a couple days and I'm at a total loss. Thanks in advance for your help!

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros