Basic MDX question

I understand the very basics of MDX. I know how to, for example, get
some set of measures as columns with some dimension on the Rows.
However, I need to do something more complex and I just can't get my
head around it.
What I'm trying to do is compare some given measure for various periods.
For example, say you have a measure [Sales] and a dimenion called
[Company Regions], the query to get the sales numbers for the company
regions basically looks like this:
with member [Date Dim].[Date Range] as
'Aggregate( {[Date Dim].[2004].[M05]:[Date Dim].[2004].[M06]}'
select [Measures].[Sales] ON COLUMNS,
Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE)
from myCube
where ([Date Dim].[Date Range])
That gives me the sales numbers for the months May and June.
What I need to do is that same basic query but I want to compare the
Date Range with last year. I just can't grasp how to make it show up in
the columns. I've read about cousin() and parallelperiods() but all the
examples return the period itself, not some measure in that period.
Can someone point me in the right direction?
Zach
Try it with a calculated member:
For example:
WITH MEMBER [Measures].[Sales_LastYear] as 'Sum([Date Dim].[2003],
[Measures].[Sales])'
MEMBER [Measures].[Sales_ThisYear] as 'Sum( {[Date Dim].[2004].[M05],[Date
Dim].[2004].[M06]}, [Measures].[Sales])'
MEMBER [Measures].[Sales_Difference] as ' [Measures].[Sales_ThisYear] -
[Measures].[Sales_LastYear]'
SELECT
{[Measures].[Sales_LastYear],[Measures].[Sales_ThisYear],[Measures].[Sales_D
ifference]} on COLUMNS,
Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE) on ROWS
from myCube
Michael
"Zach Wells" <no_zwells_spam@.ain1.com> schrieb im Newsbeitrag
news:%2366CkJyUEHA.3336@.TK2MSFTNGP11.phx.gbl...
> I understand the very basics of MDX. I know how to, for example, get
> some set of measures as columns with some dimension on the Rows.
> However, I need to do something more complex and I just can't get my
> head around it.
> What I'm trying to do is compare some given measure for various periods.
> For example, say you have a measure [Sales] and a dimenion called
> [Company Regions], the query to get the sales numbers for the company
> regions basically looks like this:
> with member [Date Dim].[Date Range] as
> 'Aggregate( {[Date Dim].[2004].[M05]:[Date Dim].[2004].[M06]}'
> select [Measures].[Sales] ON COLUMNS,
> Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE)
> from myCube
> where ([Date Dim].[Date Range])
> That gives me the sales numbers for the months May and June.
> What I need to do is that same basic query but I want to compare the
> Date Range with last year. I just can't grasp how to make it show up in
> the columns. I've read about cousin() and parallelperiods() but all the
> examples return the period itself, not some measure in that period.
> Can someone point me in the right direction?
> Zach

Basic LIKE question?

not sure why I am having trouble here but I am using the following WHERE clause expecting to find all rows where any one of the the three keywords are present.

...WHERE Company.L_Keywords LIKE '%metal%' AND Company.L_Keywords LIKE '%tile%' AND Company.L_Keywords LIKE '%ceramic%'

however it appears to finding only the rows where all three words are present in the L_Keywords field

never mind - getting a little late - mind melt.

|||

No problem, it happens to all of us.

For posterity, and for others who need the answer, the OR keyword should be used rather than AND.

Don

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
Regards
Johan
Johan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan
|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>
|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||Yes, it goes through 2 Indes Seeks, one for each table.
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>
|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>
|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh
|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:
> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > Hi!
> > I have a database wich is about 40GB. One table is very big (and many
> > rows),
> > about 20GB.
> > I can see in Sql Profiler that some questions are taking a few minutes to
> > end and during this time the processors are going 100% (sqlserver.exe).
> > After I did a maintenance job "Reorganize data and index pages" the same
> > questions ended in a few seconds only and no processor activity. Now a
> > week
> > later I have the same problem. I cannot run the maintenance job "online"
> > because it's causing locks and heavy processor activity.
> > Can I solve this problem in any other way than "Reorganize data and index
> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> > not
> > help.
> > --
> > Regards
> > Johan
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> Johan
>> Identify a slow running queries. Get the execution plan of them and see
>> does the optimizer available to use index or it doesn't
>>
>>
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> > Hi!
>> > I have a database wich is about 40GB. One table is very big (and many
>> > rows),
>> > about 20GB.
>> > I can see in Sql Profiler that some questions are taking a few minutes
>> > to
>> > end and during this time the processors are going 100% (sqlserver.exe).
>> > After I did a maintenance job "Reorganize data and index pages" the
>> > same
>> > questions ended in a few seconds only and no processor activity. Now a
>> > week
>> > later I have the same problem. I cannot run the maintenance job
>> > "online"
>> > because it's causing locks and heavy processor activity.
>> > Can I solve this problem in any other way than "Reorganize data and
>> > index
>> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> > did
>> > not
>> > help.
>> > --
>> > Regards
>> > Johan
>>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:
> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >I have identified a slow running query and run it in the execution plan.
> > The query (a join query) runs through 2 tables, both have one clustered
> > index and several non-clustered indexes.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Johan
> >>
> >> Identify a slow running queries. Get the execution plan of them and see
> >> does the optimizer available to use index or it doesn't
> >>
> >>
> >>
> >>
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> > Hi!
> >> > I have a database wich is about 40GB. One table is very big (and many
> >> > rows),
> >> > about 20GB.
> >> > I can see in Sql Profiler that some questions are taking a few minutes
> >> > to
> >> > end and during this time the processors are going 100% (sqlserver.exe).
> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> > same
> >> > questions ended in a few seconds only and no processor activity. Now a
> >> > week
> >> > later I have the same problem. I cannot run the maintenance job
> >> > "online"
> >> > because it's causing locks and heavy processor activity.
> >> > Can I solve this problem in any other way than "Reorganize data and
> >> > index
> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> > did
> >> > not
> >> > help.
> >> > --
> >> > Regards
> >> > Johan
> >>
> >>
> >>
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>> And the optimizer uses indexes to create an execution plan
>> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>> >I have identified a slow running query and run it in the execution plan.
>> > The query (a join query) runs through 2 tables, both have one clustered
>> > index and several non-clustered indexes.
>> > --
>> > Regards
>> > Johan
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Johan
>> >>
>> >> Identify a slow running queries. Get the execution plan of them and
>> >> see
>> >> does the optimizer available to use index or it doesn't
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
>> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>> >> > Hi!
>> >> > I have a database wich is about 40GB. One table is very big (and
>> >> > many
>> >> > rows),
>> >> > about 20GB.
>> >> > I can see in Sql Profiler that some questions are taking a few
>> >> > minutes
>> >> > to
>> >> > end and during this time the processors are going 100%
>> >> > (sqlserver.exe).
>> >> > After I did a maintenance job "Reorganize data and index pages" the
>> >> > same
>> >> > questions ended in a few seconds only and no processor activity. Now
>> >> > a
>> >> > week
>> >> > later I have the same problem. I cannot run the maintenance job
>> >> > "online"
>> >> > because it's causing locks and heavy processor activity.
>> >> > Can I solve this problem in any other way than "Reorganize data and
>> >> > index
>> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
>> >> > did
>> >> > not
>> >> > help.
>> >> > --
>> >> > Regards
>> >> > Johan
>> >>
>> >>
>> >>
>>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:
> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > Yes, it goes through 2 Indes Seeks, one for each table.
> > --
> > Regards
> > Johan
> >
> >
> > "Uri Dimant" wrote:
> >
> >> And the optimizer uses indexes to create an execution plan
> >>
> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> >> >I have identified a slow running query and run it in the execution plan.
> >> > The query (a join query) runs through 2 tables, both have one clustered
> >> > index and several non-clustered indexes.
> >> > --
> >> > Regards
> >> > Johan
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Johan
> >> >>
> >> >> Identify a slow running queries. Get the execution plan of them and
> >> >> see
> >> >> does the optimizer available to use index or it doesn't
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> >> >> > Hi!
> >> >> > I have a database wich is about 40GB. One table is very big (and
> >> >> > many
> >> >> > rows),
> >> >> > about 20GB.
> >> >> > I can see in Sql Profiler that some questions are taking a few
> >> >> > minutes
> >> >> > to
> >> >> > end and during this time the processors are going 100%
> >> >> > (sqlserver.exe).
> >> >> > After I did a maintenance job "Reorganize data and index pages" the
> >> >> > same
> >> >> > questions ended in a few seconds only and no processor activity. Now
> >> >> > a
> >> >> > week
> >> >> > later I have the same problem. I cannot run the maintenance job
> >> >> > "online"
> >> >> > because it's causing locks and heavy processor activity.
> >> >> > Can I solve this problem in any other way than "Reorganize data and
> >> >> > index
> >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> >> >> > did
> >> >> > not
> >> >> > help.
> >> >> > --
> >> >> > Regards
> >> >> > Johan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows),
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
> > Well, how much data does the query return ?
> >
> > Perhaps , you need to re-design the indexes to be more efficient
> >
> > Look at SET STATISTICS IO ON commad. (Logical reads)
> >
> >
> >
> >
> > "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
> > > Yes, it goes through 2 Indes Seeks, one for each table.
> > > --
> > > Regards
> > > Johan
> > >
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> And the optimizer uses indexes to create an execution plan
> > >>
> > >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
> > >> >I have identified a slow running query and run it in the execution plan.
> > >> > The query (a join query) runs through 2 tables, both have one clustered
> > >> > index and several non-clustered indexes.
> > >> > --
> > >> > Regards
> > >> > Johan
> > >> >
> > >> >
> > >> > "Uri Dimant" wrote:
> > >> >
> > >> >> Johan
> > >> >>
> > >> >> Identify a slow running queries. Get the execution plan of them and
> > >> >> see
> > >> >> does the optimizer available to use index or it doesn't
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >>
> > >> >> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> > >> >> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> > >> >> > Hi!
> > >> >> > I have a database wich is about 40GB. One table is very big (and
> > >> >> > many
> > >> >> > rows),
> > >> >> > about 20GB.
> > >> >> > I can see in Sql Profiler that some questions are taking a few
> > >> >> > minutes
> > >> >> > to
> > >> >> > end and during this time the processors are going 100%
> > >> >> > (sqlserver.exe).
> > >> >> > After I did a maintenance job "Reorganize data and index pages" the
> > >> >> > same
> > >> >> > questions ended in a few seconds only and no processor activity. Now
> > >> >> > a
> > >> >> > week
> > >> >> > later I have the same problem. I cannot run the maintenance job
> > >> >> > "online"
> > >> >> > because it's causing locks and heavy processor activity.
> > >> >> > Can I solve this problem in any other way than "Reorganize data and
> > >> >> > index
> > >> >> > pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it
> > >> >> > did
> > >> >> > not
> > >> >> > help.
> > >> >> > --
> > >> >> > Regards
> > >> >> > Johan
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Bad performance in some questions (100% CPU)

Hi!
I have a database wich is about 40GB. One table is very big (and many rows),
about 20GB.
I can see in Sql Profiler that some questions are taking a few minutes to
end and during this time the processors are going 100% (sqlserver.exe).
After I did a maintenance job "Reorganize data and index pages" the same
questions ended in a few seconds only and no processor activity. Now a week
later I have the same problem. I cannot run the maintenance job "online"
because it's causing locks and heavy processor activity.
Can I solve this problem in any other way than "Reorganize data and index
pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did not
help.
--
Regards
JohanJohan
Identify a slow running queries. Get the execution plan of them and see
does the optimizer available to use index or it doesn't
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
> Hi!
> I have a database wich is about 40GB. One table is very big (and many
> rows),
> about 20GB.
> I can see in Sql Profiler that some questions are taking a few minutes to
> end and during this time the processors are going 100% (sqlserver.exe).
> After I did a maintenance job "Reorganize data and index pages" the same
> questions ended in a few seconds only and no processor activity. Now a
> week
> later I have the same problem. I cannot run the maintenance job "online"
> because it's causing locks and heavy processor activity.
> Can I solve this problem in any other way than "Reorganize data and index
> pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did
> not
> help.
> --
> Regards
> Johan|||I have identified a slow running query and run it in the execution plan.
The query (a join query) runs through 2 tables, both have one clustered
index and several non-clustered indexes.
--
Regards
Johan
"Uri Dimant" wrote:

> Johan
> Identify a slow running queries. Get the execution plan of them and see
> does the optimizer available to use index or it doesn't
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:496C6989-AD94-4BCE-A158-45D016F3A273@.microsoft.com...
>
>|||And the optimizer uses indexes to create an execution plan
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...[vbcol=seagreen]
>I have identified a slow running query and run it in the execution plan.
> The query (a join query) runs through 2 tables, both have one clustered
> index and several non-clustered indexes.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||Yes, it goes through 2 Indes Seeks, one for each table.
--
Regards
Johan
"Uri Dimant" wrote:

> And the optimizer uses indexes to create an execution plan
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:9A0ADD64-D50C-4D0E-AD38-9B10A7FAEA1D@.microsoft.com...
>
>|||Well, how much data does the query return ?
Perhaps , you need to re-design the indexes to be more efficient
Look at SET STATISTICS IO ON commad. (Logical reads)
"JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...[vbcol=seagreen]
> Yes, it goes through 2 Indes Seeks, one for each table.
> --
> Regards
> Johan
>
> "Uri Dimant" wrote:
>|||The questions returns no data. 0 rows...
I did get this when I run with SET STATISTICS IO ON:
Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0,
read-ahead reads 0.
Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
read-ahead reads 0.
Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads 0,
read-ahead reads 0.
--
Regards
Johan
"Uri Dimant" wrote:

> Well, how much data does the query return ?
> Perhaps , you need to re-design the indexes to be more efficient
> Look at SET STATISTICS IO ON commad. (Logical reads)
>
>
> "JohanSql" <JohanSql@.discussions.microsoft.com> wrote in message
> news:21AD1D91-FFD9-468E-B0D6-B225FFC95118@.microsoft.com...
>
>|||On Wed, 2 Nov 2005 04:26:14 -0800, "JohanSql"
<JohanSql@.discussions.microsoft.com> wrote:
>I have a database wich is about 40GB. One table is very big (and many rows)
,
>about 20GB.
>I can see in Sql Profiler that some questions are taking a few minutes to
>end and during this time the processors are going 100% (sqlserver.exe).
>After I did a maintenance job "Reorganize data and index pages" the same
>questions ended in a few seconds only and no processor activity. Now a week
>later I have the same problem. I cannot run the maintenance job "online"
>because it's causing locks and heavy processor activity.
>Can I solve this problem in any other way than "Reorganize data and index
>pages"? I tried to run DBCC INDEXDEFRAG and UPDATE STATISTICS but it did no
t
>help.
Seems odd that you go from a few seconds to several minutes.
I guess that many new records are added during the week?
Are many records also deleted?
What is the primary key on the big table?
Can you post the source for the query?
Josh|||It would be useful to see the query plan, some DDL and the purpose of
the query.
Do you have foreign keys between the tables you are joining?
Does the query use parallelism during execution? What SQL-Server Service
pack are you running?
If the query plan is using loop joins, then it would mean that each
lookup requirs (on average) 24 logical reads. That does not sound
optimal. Index tuning could probably improve that.
Anyway, your post does not contain enough information to make an
educated guess, but just barely enough to make wild guesses.
Gert-Jan
JohanSql wrote:[vbcol=seagreen]
> The questions returns no data. 0 rows...
> I did get this when I run with SET STATISTICS IO ON:
> Table 'Worktable'. Scan count 6599, logical reads 158759, physical reads 0
,
> read-ahead reads 0.
> Table 'Table1'. Scan count 16, logical reads 712, physical reads 0,
> read-ahead reads 0.
> Table 'Table2'. Scan count 688, logical reads 14286, physical reads 0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 7935, logical reads 188151, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 8680, logical reads 204541, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9218, logical reads 216377, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10121, logical reads 236243, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 9196, logical reads 215893, physical reads 0
,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 10211, logical reads 238223, physical reads
0,
> read-ahead reads 0.
> Table 'Worktable'. Scan count 11959, logical reads 276679, physical reads
0,
> read-ahead reads 0.
> --
> Regards
> Johan
> "Uri Dimant" wrote:
>

Backups running concurrently with db maintenance

I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?
You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>

Backups running concurrently with db maintenance

I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
--
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>

Backups running concurrently with db maintenance

I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>