Re: tuning question, relation between indexes and "for xml path", and

When you dropped the indexes and recreated some, the order they were
dropped/recreated could have an effect. Try rebuilding your nonclustered
indexes now that the clustered indexes are in place and see if that helps.

"Pedja" <Pedja@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F6C02C5F-4199-4E3B-B74D-20D16317389F@xxxxxxxxxxxxxxxx
,Hi,
I'm facing very strange problem here. I made index analysis by quering
dynamic views, and found that bunch of indexes is not being used at all.
Beside that, I found bunch of indexes which are duplicated. For example,
on
the same set of columns, there are 2 nonclustered indexes created, primary
key constraint and "regular one". That made me think that whoever created
this database didn't know that primary key automatically creates an index.
Therefore, I decided to drop redundant indexes, and indexes which are not
being used (according to dynamic views), hoping that it will improve
insert/update performace. I also changed some of indexes from nonclusted
to
clustered.
However, here is where the problem begins: after all these changes, stored
procedures (selects with "for xml path") started consuming CPU
extensively
sometimes causing server to have CPU peaks of 100% for 10 minutes or so!
Am I
missing something here (you're guessing, I'm not an expert for XML - code,
XML processing, no experience at all with XML)? Changing nonclustered
index
to clustered on the same set of columns should't be a problem because
these
stored procedures are selects.
Sql server is 2005, service pack 2. Server has 4 processors, and beside
long
CPU in activity monitor for these processes, I noticed occasional
"suspened"
status and CXPacket and Network waitings in "Wait type"...
Any ideas?
Thanks


.

Relevant Pages

  • Re: tuning question, relation between indexes and "for xml path", and
    ... Is it only a problem with high CPU usage or we have lots of reads/writes too? ... procedures (selects with "for xml path") started consuming CPU extensively ... stored procedures are selects. ... Sql server is 2005, service pack 2. ...
    (microsoft.public.sqlserver.xml)
  • Re: How Best to Store this Data
    ... One one hand, the SQL way is familiar, but as you said, it becomes a pain to ... The Xml alternative would be an interesting and different way to do this ( ... of keeping up stored procedures and keeping evrything in SQL. ...
    (microsoft.public.dotnet.framework.aspnet)
  • [ANNOUNCEMENT]:VTD-XML released under GPL
    ... Java-base XML processing API licensed under GPL ... The core technology of VTD-XML is a binary format specification ... that one can save/load VTD to/from the disk along with the XML documents ...
    (comp.lang.java.databases)
  • [ANNOUNCEMENT}: VTD-XML released under GPL
    ... Java-base XML processing API licensed under GPL ... The core technology of VTD-XML is a binary format specification ... that one can save/load VTD to/from the disk along with the XML documents ...
    (comp.lang.java.programmer)
  • [ANNOUNCEMENT] VTD-XML released under GPL
    ... Java-base XML processing API licensed under GPL ... is maintained intact in memory by the processing model. ... A VTD record is immune to the overhead because ...
    (comp.lang.java.programmer)