Finding deatail Deadlock info IN SQl Server 2005 Standard Edtion

We are using SQL Server 2005 with SP2.Originally, we seemed to be having a lot of unnecessary lock escalation occurring to the page and table level.

I would like to track down all storedprocedure/queries or function that are being casue of deadlock in sql server 2005 with all deadlock input like transactionID ,command ran,loginID etc.But we have to need to find these information on SQL Server 2005 standard edtion.

Is there any tool,command or something like this which track down these information blindly in sql server 2005 without effecting system performance seriously.

Ahamd Drshen

Hi,

When executing DBCC TRACEON (1204,-1) in a command window, you're enabling the deadlock tracing option. (http://msdn2.microsoft.com/en-us/library/ms188396.aspx) When a deadlock occurs, you can find details in the logs. You can also try using the option 'with nolock' in your sql statements (SELECT * FROM Employees WITH (NOLOCK)).

Regards,

Remco

|||will it track all deadlock resource information SQL Server Errors log ?|||it will show all conflicting locks and its commands.|||

Use SQL Server Profiler with DeadLock Graph Event.

Or use stored procedures sp_create_trace and sp_trace_setevent for collecting profiler events without graphical interface.

@.eventId for DeadLock Graph = 148

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > Take the authors table as an example in the pubs database...
> >
> > How can I find how many levels within the B-Tree from the root to the
> level
> > that contains the data rows (clustered index) ? Also how many pages at
> each
> > level within that B-Tree ?
> >
> > Will i have 2 B-Trees now, one for the clustered index and one for the
> > non-clustered ? Using SQL 2000
> >
> >
>|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Have a look into DBCC SHOWCONTIG command in books online.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > > Take the authors table as an example in the pubs database...
> > >
> > > How can I find how many levels within the B-Tree from the root to the
> > level
> > > that contains the data rows (clustered index) ? Also how many pages at
> > each
> > > level within that B-Tree ?
> > >
> > > Will i have 2 B-Trees now, one for the clustered index and one for the
> > > non-clustered ? Using SQL 2000
> > >
> > >
> >
> >
>|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the level
> that contains the data rows (clustered index) ? Also how many pages at each
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
--
(Please reply only to the newsgroup)

Find all dimensions with calculated members using AdomdClient

Hi,

I need to find all dimensions in a cube that have calculated members but I can't afford to call GetMembers on every hierarchy level.

Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient

Thanks,

James

Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members.

> Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient

Not possible.

> Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members

Can you please clarify in more details what is the special about calculated members so that you want to know in advance whether hierarchy has them.

|||

Hi Mosha,

Thanks for the reply, I believe our very own Dave Grant showed you some of RSinteract’s MDX at PASS in Seattle.

We decided that placing the filters in the sub-query allowed us to provide more user friendly totalling (i.e. when filtering row or column attributes). However calculated members are not allowed in sub-queries, so we need to know which Dimension Attributes contain Calc Members and place any filters inline or in a where clause. Our current solution is to have an administrator run an AMO application which saves dimensions with calc members to a config file (AMO must be run with admin privileges).

Cheers,

James

|||What I wonder is why do you want to exclude the entire hierarchy just because it may have calculated members. It seems like if you just wrap the sets that you put inside subselects with StripCalculatedMembers function call, both you and your users will be better of.