Long table locks

Hi
There is an application that runs on sql server.
The application selects/updates some few tables frequently
Once there is even a select on this table .It blocks other users
sometimes for very long.
Is there anything that can be done to reduce this?
The table has 18000 rows and does not seem to have an index
I thought indexing might help but 18000 rows without an index is
no reason for 30 minutes of lock time.
I will appreciate your help as usual
VinceVincento Harris (wumutek@.yahoo.com) writes:
> There is an application that runs on sql server.
> The application selects/updates some few tables frequently
> Once there is even a select on this table .It blocks other users
> sometimes for very long.
> Is there anything that can be done to reduce this?
> The table has 18000 rows and does not seem to have an index
> I thought indexing might help but 18000 rows without an index is
> no reason for 30 minutes of lock time.

Yes, something can probably be done, but the information you've provided
is not sufficient to say what should be done.

The key to nail down performance problems is information. Exactly
what is taking long time? And are there any transactions that are
open for all this time? You might get some useful information by
tracing this process with the Profiler.

Is this ia an in-house app, or a something you've bought from a vendor?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Long MDX statement locks up report designer. Unknown bug?

The following query string locks up report designer on dev studio. Is there
a limitation or unknown bug that causes report designer to not be able to
parse this length of statement into xml? ( It's not too long for the query
designer text box, since the whole statement fits. I've had other queries
that needed to be redone do to length issues. something like 2k+ character
limit ) The query runs fine with the actual values instead of the parameters
that are being used here.
="WITH " &
"SET [TOP 20 CLIENTS ALL BILL TYPES] AS
'Filter([Client].[Client].Members,[Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """2D""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """6A""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """2A""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """HV""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VK""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VN""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """WM""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """0D""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """V5""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VP""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """R2""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """RN""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """VT""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """CH""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """NX""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """T7""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """YV""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """N4""" & " OR [Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """MX""" & " OR
INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "), " &
"""WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
"""Client Name""" & "), " & """WEST WHOLESALE""" & "))' " &
"SET [All Clients] as '[Client].[(All)].Members' " &
"MEMBER [Measures].[Pror#] as 'IIF([Client].CurrentMember IS [Top 20
SubTotal], " & """@.@.""" & ",IIF([Client].CurrentMember IS [All Other], " &
"""^^""" & ",IIF([Client].CurrentMember IS [Total], " & """**""" & ",
[Client].CurrentMember.Properties(" & """Client""" & "))))' " &
"SET [Weeks] as '{LastPeriods(49,[Margin Time].[Calendar].[Calendar Day].["
& Parameters!pWeekEndingDate.Label & "])}' " &
"SET [Months] as '{LastPeriods(6, [Margin Time].[Calendar].[Calendar
Month].[" & code.MonthYearPeriod(Parameters!pWeekEndingDate.Label, 0) & "])}'
" &
"SET [Expenses] as '{[Margin].&[Commission Exp].&[STD],[Margin].&[Direct
Mail Exp], [Margin].&[Internet Exp], [Margin].&[Memberlink Exp],
[Margin].&[Telemarketing Cost] }' " &
"MEMBER [Measures].[Period Ending DisplayName] as 'IIF([Margin
Time].[Calendar].CurrentMember.Level.Name = " & """Calendar Day""" & ", " &
"""Week """ & " + CSTR(VBA!DatePart(" & """ww""" & ", [Margin
Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
Time].[Calendar].CurrentMember.Name)), " & """Month """ & " +
CSTR(VBA!DatePart(" & """m""" & ", [Margin
Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
Time].[Calendar].CurrentMember.Name)))' " &
"MEMBER [Measures].[Client DisplayName] as 'IIF([Client].CurrentMember IS
[Top 20 SubTotal], " & """Top 20 SubTotal""" & ", IIF([Client].CurrentMember
IS [All Other], " & """All Other""" & ", IIF([Client].CurrentMember IS
[Total], " & """Total""" & ", IIF([Client].CurrentMember.Properties(" &
"""Client Group""" & ") <> " & """WEST TELESERVICES""" & ",
[Client].CurrentMember.Properties(" & """Client Group""" & "), " & """WEST
RETAIL""" & "))))' " &
"MEMBER [Measures].[Column Order] as 'IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(5)), 6, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(4)), 5, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(3)), 4, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(2)), 3, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(1)), 2, IIF(([Margin
Time].[Calendar].CurrentMember IS [Months].Item(0)), 1, 9))))))' " &
"MEMBER [Client].[Top 20 SubTotal] as '[Top 20 SubTotal]' " &
"MEMBER [Client].[All Other] as '[All Other]' " &
"MEMBER [Client].[Total] as '[Total]' " &
"MEMBER [Measures].[Rank] as ' IIF([Client].CurrentMember IS [Top 20
SubTotal], 21, IIF([Client].CurrentMember IS [All Other], 22,
IIF([Client].CurrentMember IS [Total],
23,IIF(INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "), "
& """WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
"""Client Name""" & "), " & """WEST WHOLESALE""" & ") ,
1,IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """2D"""
& " , 2, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""6A""" & " , 3, IIF([Client].CurrentMember.Properties(" & """Client""" & ")
= " & """2A""" & " , 4, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """HV""" & " , 5,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VK""" &
" , 6, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""VN""" & " , 7, IIF([Client].CurrentMember.Properties(" & """Client""" & ")
= " & """WM""" & " , 8, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """0D""" & " , 9,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """V5""" &
" , 10, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""VP""" & " , 11, IIF([Client].CurrentMember.Properties(" & """Client""" &
") = " & """R2""" & " , 12, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """RN""" & " , 13,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VT""" &
" , 14, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""CH""" & " , 15, IIF([Client].CurrentMember.Properties(" & """Client""" &
") = " & """NX""" & " , 16, IIF([Client].CurrentMember.Properties(" &
"""Client""" & ") = " & """T7""" & " , 17,
IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """YV""" &
" , 18, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""N4""" & " , 19, IIF([Client].CurrentMember.Properties(" & """Client""" &
") = " & """MX""" & " , 20, 99)))))))))))))))))))))))' " &
"MEMBER [Measures].[Actual] AS 'IIF([Client].CurrentMember IS [Top 20
SubTotal], SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
[Measures].[Dollars]) / 1000, IIF([Client].CurrentMember IS [All Other],
((SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars])) -
(SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
[Measures].[Dollars]))) / 1000, IIF([Client].CurrentMember IS [Total],
SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars]) / 1000,
SUM({ [Expenses] }, [Measures].[Dollars]) / 1000)))' " &
"SELECT {[Measures].[Rank], [Measures].[Column Order], [Measures].[Client
DisplayName], [Measures].[Pror#], [Measures].[Period Ending DisplayName],
[Measures].[Actual], [Measures].[Dollars] } ON COLUMNS, " &
"NON EMPTY {CROSSJOIN( {[TOP 20 CLIENTS ALL BILL TYPES], [Client].[Top 20
SubTotal] , [Client].[All Other], [Client].[Total] }, {[Weeks], [Months] } )
} ON ROWS " &
"FROM [Margins] WHERE ([Bill Type].[Bill Group].&[New] )"Created named set on the cube to shorten the length. No further help
needed.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:C3338E73-870C-4425-B40E-99C8DCCC1D5E@.microsoft.com...
> The following query string locks up report designer on dev studio. Is
there
> a limitation or unknown bug that causes report designer to not be able to
> parse this length of statement into xml? ( It's not too long for the query
> designer text box, since the whole statement fits. I've had other queries
> that needed to be redone do to length issues. something like 2k+
character
> limit ) The query runs fine with the actual values instead of the
parameters
> that are being used here.
> ="WITH " &
> "SET [TOP 20 CLIENTS ALL BILL TYPES] AS
> 'Filter([Client].[Client].Members,[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """2D""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """6A""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """2A""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """HV""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VK""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VN""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """WM""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """0D""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """V5""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VP""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """R2""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """RN""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """VT""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """CH""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """NX""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """T7""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """YV""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """N4""" & " OR
[Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """MX""" & " OR
> INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "), " &
> """WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
> """Client Name""" & "), " & """WEST WHOLESALE""" & "))' " &
> "SET [All Clients] as '[Client].[(All)].Members' " &
> "MEMBER [Measures].[Pror#] as 'IIF([Client].CurrentMember IS [Top 20
> SubTotal], " & """@.@.""" & ",IIF([Client].CurrentMember IS [All Other], " &
> """^^""" & ",IIF([Client].CurrentMember IS [Total], " & """**""" & ",
> [Client].CurrentMember.Properties(" & """Client""" & "))))' " &
> "SET [Weeks] as '{LastPeriods(49,[Margin Time].[Calendar].[Calendar
Day].["
> & Parameters!pWeekEndingDate.Label & "])}' " &
> "SET [Months] as '{LastPeriods(6, [Margin Time].[Calendar].[Calendar
> Month].[" & code.MonthYearPeriod(Parameters!pWeekEndingDate.Label, 0) &
"])}'
> " &
> "SET [Expenses] as '{[Margin].&[Commission Exp].&[STD],[Margin].&[Direct
> Mail Exp], [Margin].&[Internet Exp], [Margin].&[Memberlink Exp],
> [Margin].&[Telemarketing Cost] }' " &
> "MEMBER [Measures].[Period Ending DisplayName] as 'IIF([Margin
> Time].[Calendar].CurrentMember.Level.Name = " & """Calendar Day""" & ", "
&
> """Week """ & " + CSTR(VBA!DatePart(" & """ww""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
> CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)), " & """Month """ & " +
> CSTR(VBA!DatePart(" & """m""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)) + " & """ of """ & " +
> CSTR(VBA!DatePart(" & """yyyy""" & ", [Margin
> Time].[Calendar].CurrentMember.Name)))' " &
> "MEMBER [Measures].[Client DisplayName] as 'IIF([Client].CurrentMember IS
> [Top 20 SubTotal], " & """Top 20 SubTotal""" & ",
IIF([Client].CurrentMember
> IS [All Other], " & """All Other""" & ", IIF([Client].CurrentMember IS
> [Total], " & """Total""" & ", IIF([Client].CurrentMember.Properties(" &
> """Client Group""" & ") <> " & """WEST TELESERVICES""" & ",
> [Client].CurrentMember.Properties(" & """Client Group""" & "), " & """WEST
> RETAIL""" & "))))' " &
> "MEMBER [Measures].[Column Order] as 'IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(5)), 6, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(4)), 5, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(3)), 4, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(2)), 3, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(1)), 2, IIF(([Margin
> Time].[Calendar].CurrentMember IS [Months].Item(0)), 1, 9))))))' " &
> "MEMBER [Client].[Top 20 SubTotal] as '[Top 20 SubTotal]' " &
> "MEMBER [Client].[All Other] as '[All Other]' " &
> "MEMBER [Client].[Total] as '[Total]' " &
> "MEMBER [Measures].[Rank] as ' IIF([Client].CurrentMember IS [Top 20
> SubTotal], 21, IIF([Client].CurrentMember IS [All Other], 22,
> IIF([Client].CurrentMember IS [Total],
> 23,IIF(INSTR([Client].CurrentMember.Properties(" & """Client Name""" & "),
"
> & """WEST RETAIL""" & ") OR INSTR([Client].CurrentMember.Properties(" &
> """Client Name""" & "), " & """WEST WHOLESALE""" & ") ,
> 1,IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
"""2D"""
> & " , 2, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """6A""" & " , 3, IIF([Client].CurrentMember.Properties(" & """Client""" &
")
> = " & """2A""" & " , 4, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """HV""" & " , 5,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VK"""
&
> " , 6, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """VN""" & " , 7, IIF([Client].CurrentMember.Properties(" & """Client""" &
")
> = " & """WM""" & " , 8, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """0D""" & " , 9,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """V5"""
&
> " , 10, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """VP""" & " , 11, IIF([Client].CurrentMember.Properties(" & """Client"""
&
> ") = " & """R2""" & " , 12, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """RN""" & " , 13,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """VT"""
&
> " , 14, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """CH""" & " , 15, IIF([Client].CurrentMember.Properties(" & """Client"""
&
> ") = " & """NX""" & " , 16, IIF([Client].CurrentMember.Properties(" &
> """Client""" & ") = " & """T7""" & " , 17,
> IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " & """YV"""
&
> " , 18, IIF([Client].CurrentMember.Properties(" & """Client""" & ") = " &
> """N4""" & " , 19, IIF([Client].CurrentMember.Properties(" & """Client"""
&
> ") = " & """MX""" & " , 20, 99)))))))))))))))))))))))' " &
> "MEMBER [Measures].[Actual] AS 'IIF([Client].CurrentMember IS [Top 20
> SubTotal], SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
> [Measures].[Dollars]) / 1000, IIF([Client].CurrentMember IS [All Other],
> ((SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars])) -
> (SUM({Crossjoin([TOP 20 CLIENTS ALL BILL TYPES], [Expenses]) },
> [Measures].[Dollars]))) / 1000, IIF([Client].CurrentMember IS [Total],
> SUM({Crossjoin( [All Clients], [Expenses]) }, [Measures].[Dollars]) /
1000,
> SUM({ [Expenses] }, [Measures].[Dollars]) / 1000)))' " &
> "SELECT {[Measures].[Rank], [Measures].[Column Order], [Measures].[Client
> DisplayName], [Measures].[Pror#], [Measures].[Period Ending DisplayName],
> [Measures].[Actual], [Measures].[Dollars] } ON COLUMNS, " &
> "NON EMPTY {CROSSJOIN( {[TOP 20 CLIENTS ALL BILL TYPES], [Client].[Top 20
> SubTotal] , [Client].[All Other], [Client].[Total] }, {[Weeks],
[Months] } )
> } ON ROWS " &
> "FROM [Margins] WHERE ([Bill Type].[Bill Group].&[New] )"
>