Solved

Query not working on some tables

Posted on 2016-07-27
7
36 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 500 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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