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
Solved

Query not working on some tables

Posted on 2016-07-27
7
32 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
  • 4
  • 3
7 Comments
 
LVL 26

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 26

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 26

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2 IIF's in Access query 25 44
Syntax Issue with SSIS module 26 100
Negative isnull? 3 14
partitioning database after decade growth 8 19
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach 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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

791 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