[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query not working on some tables

Posted on 2016-07-27
7
Medium Priority
?
38 Views
Last Modified: 2016-07-27
Heyas,

The following query:

SELECT

IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
AND IOS.OBJECT_ID = I.OBJECT_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

Source:

http://social.technet.microsoft.com/wiki/contents/articles/25473.sql-server-dmv-to-track-page-split.aspx

Doesn't always work the error message I get is:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '('.

Any help is always welcome.

Thank you.
0
Comment
Question by:Zack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 41732266
I cut and paste it and it works in SQL Server 2014.
In what context does it not work?  A different server with a different version of SQL Server?  Did you try to change something or add something to it?
0
 

Author Comment

by:Zack
ID: 41732279
Hi Chris,

In some tables in SQL Server 2008, that the odd thing is for some tables it works fine for others it gives that error message described.

Thank you.
0
 

Author Comment

by:Zack
ID: 41732284
Hi Chris,

Additional info SQL Server 2008 (100) compatibility level.

Thank you.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Assisted Solution

by:Zack
Zack earned 0 total points
ID: 41732287
Hi Chris,

CORRECTION: Additional info SQL Server 2000 (80) compatibility level. On tables that give an error when running the query. I think this is it. Can you confirm.

Thank you.
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 41732289
a little hard to confirm.
 :-)
do you know how many years it has been since I even had a SQL Server 2000 machine to touch, but I bet that is a big problem, the query uses DM_DB_INDEX_OPERATIONAL_STATS which was introduced in 2008 R2, so it is blowing up on the "(" following that.
0
 

Author Closing Comment

by:Zack
ID: 41732292
Good point Chris :), thank you for the assistance.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 41732294
You're welcome!
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question