The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

luke.bace (66) [Avatar] Offline
#1
Please post all future Errors and Corrections here
lesnic (2) [Avatar] Offline
#2
Re: Errors and Corrections
Listing 1.3 on p 13.
The SQL contains #A and #B. These appear to be unnecessary/ errors:


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
--#A
AS [Total Elapsed Duration (s)]
, execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
--#B
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.dm_exec_cached_plans as cp
on qs.plan_handle=cp.plan_handle
ian.stirk (27) [Avatar] Offline
#3
Re: Errors and Corrections
Hi,

The #letters are markers for the typesetters/publishers, they will not be in the final release of the book or the downloadable code.

Thanks
Ian
tgrignon (2) [Avatar] Offline
#4
Re: Errors and Corrections
Listing 1.4 is missing the avg_user_impact column in the select.

Right now it's

SELECT TOP 20
ROUND(avg_total_user_cost * avg_user_impact *
(user_seeks + user_scans),0) AS [Total Cost]
, statement AS TableName

in the copy of the book I have whereas it should be:

SELECT TOP 20
ROUND(avg_total_user_cost * avg_user_impact *
(user_seeks + user_scans),0) AS [Total Cost]
, avg_user_impact
, statement AS TableName
ian.stirk (27) [Avatar] Offline
#5
Re: Errors and Corrections
Hi,

Thanks for reporting this. The document has been updated accordingly.

Thanks again
Ian
brammp (3) [Avatar] Offline
#6
Re: Errors and Corrections
Page 17 - Referring to DBCC FREEPROCCACHE - SQL Server 2005 does not accept inputs to this command. Only in 2008+ can we input a plan_handle, etc...

So it may be beneficial to adjust the wording a bit to make it clear that 2005 does not accept a parameter. I think it is the second paragraph shown below, that to me, contradicts the previous paragraph by making a claim that FREEPROCCACHE accepts "either a plan_handle, sql_handle or pool_name."


"To clear the DMVs that relate to cached plans, at the server level, we use the following command:
DBCC FREEPROCCACHE. This clears all the cached plans on all databases on the server. In SQL Server
2008 this command can also be supplied with a parameter to remove a specific cached plan from the pool
of cached plans.
The parameter supplied to DBCC FREEPROCCACHE is either a plan_handle, sql_handle or pool_name.
plan_handle and sql_handle are 64bit identifiers of a query plan and batch of SQL statements respectively
that are found in various DMVs. The pool_name is the name of a Resource Governor workload group
within a resource pool."

Message was edited by:
brammp
brammp (3) [Avatar] Offline
#7
Re: Errors and Corrections
Page 27 - sp_msforeachdb -

"Luckily, Microsoft provides a stored procedure for doing this, the relatively little
documented sp_MSForEachDB."

I would specify that this is an *un*documented command, and as such, it is not guaranteed to be in the next version of SQL Server. Use at your own risk.
ian.stirk (27) [Avatar] Offline
#8
Re: Errors and Corrections
Hi,

thanks for your message. I will look at changing the document to make things clearer.

Ian
atolmeijer (11) [Avatar] Offline
#9
Re: Errors and Corrections
Page 67, Table 3.11 description describes sys.indexes. This should be sys.sysindexes. A notification that this is an obslolete view according to Microsoft could be in place.

"In the listing, you can see there are three system table are involved in identifying the current state of
index statistics, a brief description of each is shown in table 3.11.
Table 3.11 The state of your statistics
Tables Description
sys.indexes Contains details for each index e.g. name, type, row count, number
of rows changes since statistics last updated"

Message was edited by:
atolmeijer
ian.stirk (27) [Avatar] Offline
#10
Re: Errors and Corrections
Hi,

thanks for this. I will investigate...

Ian
Bendono (9) [Avatar] Offline
#11
Re: Errors and Corrections
atolmeijer,

This looks OK.
It is sys.sysindexes that is obsolete and has been replaced by sys.indexes.
The documentation for both are here:

sys.indexes
http://msdn.microsoft.com/en-us/library/ms173760.aspx

sys.sysindexes
http://msdn.microsoft.com/en-us/library/ms190283.aspx

Quote:

Contains one row for each index and table in the current database. XML indexes are not supported in this view. Partitioned tables and indexes are not fully supported in this view; _use the sys.indexes catalog view instead._
Important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Regards.
atolmeijer (11) [Avatar] Offline
#12
Re: Errors and Corrections
Hi Bendono,

No, it's not the same. sys.sysindexes has completely different fields than sys.indexes, e.g. rowcnt. Also, the query uses sys.sysindexes - as it should be -, but the explanation afterwards mentions sys.indexes instead. This is wrong / a typo.

Indeed, sys.sysindexes is marked as obsolete, that was also what I was trying to point out. It's a shame though, I want to know the number of records without doing a COUNT(*).

Regards,
Arno

Message was edited by:
atolmeijer
atolmeijer (11) [Avatar] Offline
#13
Re: Errors and Corrections
Follow up: it seems that sys.dm_db_partition_stats() canbe used to obtain the row count.
ian.stirk (27) [Avatar] Offline
#14
Re: Errors and Corrections
Hi Bendono and atolmeijer,

Thanks for raising this issue.

Because the book is so close to being published, I think it is best to use the code that currently exists. The code is correct and works successfully with SQL Server 2005 and SQL Server 2008 (and may work with SQL Server 2011).

I will however investigate an alternative, and post it here and in any future versions of the book.

Thanks again for your help!
Ian