CREATE DATABASE failed: utilization

Showing posts with label utilization. Show all posts

Thursday, March 8, 2012

CPU utilization not even

Hi
I have windows 2000 advanced server and sql server 2000 EE. It is used for
datawarehouse . A report, which used to finish in 20 minutes now it is
taking 4 hours to finish. It is same query same indexes same database. I
look at the perf counters one cpu(4) is at 70% (average)utilization others
are at 3% (average). I also checked the interupts are 560/sec, I guess it
is something abnormal, can someone help me out
Thanks,
Subbu.
Hi
In the server settings, check that Maximum Query Plan threshold is at least
5. Looks like your query is only using 1 processor. Also check the SQL
Server is configured to use all processors.
Both are on the Processor tab on the Properties page for the Server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:#P4yg0A9EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have windows 2000 advanced server and sql server 2000 EE. It is used for
> datawarehouse . A report, which used to finish in 20 minutes now it is
> taking 4 hours to finish. It is same query same indexes same database. I
> look at the perf counters one cpu(4) is at 70% (average)utilization others
> are at 3% (average). I also checked the interupts are 560/sec, I guess
it
> is something abnormal, can someone help me out
> Thanks,
> Subbu.
>

CPU utilization not even

Hi
I have Windows 2000 advanced server and sql server 2000 EE. It is used for
datawarehouse . A report, which used to finish in 20 minutes now it is
taking 4 hours to finish. It is same query same indexes same database. I
look at the perf counters one cpu(4) is at 70% (average)utilization others
are at 3% (average). I also checked the interupts are 560/sec, I guess it
is something abnormal, can someone help me out
Thanks,
Subbu.Hi
In the server settings, check that Maximum Query Plan threshold is at least
5. Looks like your query is only using 1 processor. Also check the SQL
Server is configured to use all processors.
Both are on the Processor tab on the Properties page for the Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:#P4yg0A9EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have Windows 2000 advanced server and sql server 2000 EE. It is used for
> datawarehouse . A report, which used to finish in 20 minutes now it is
> taking 4 hours to finish. It is same query same indexes same database. I
> look at the perf counters one cpu(4) is at 70% (average)utilization others
> are at 3% (average). I also checked the interupts are 560/sec, I guess
it
> is something abnormal, can someone help me out
> Thanks,
> Subbu.
>

CPU utilization not even

Hi
I have windows 2000 advanced server and sql server 2000 EE. It is used for
datawarehouse . A report, which used to finish in 20 minutes now it is
taking 4 hours to finish. It is same query same indexes same database. I
look at the perf counters one cpu(4) is at 70% (average)utilization others
are at 3% (average). I also checked the interupts are 560/sec, I guess it
is something abnormal, can someone help me out
Thanks,
Subbu.Hi
In the server settings, check that Maximum Query Plan threshold is at least
5. Looks like your query is only using 1 processor. Also check the SQL
Server is configured to use all processors.
Both are on the Processor tab on the Properties page for the Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:#P4yg0A9EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have windows 2000 advanced server and sql server 2000 EE. It is used for
> datawarehouse . A report, which used to finish in 20 minutes now it is
> taking 4 hours to finish. It is same query same indexes same database. I
> look at the perf counters one cpu(4) is at 70% (average)utilization others
> are at 3% (average). I also checked the interupts are 560/sec, I guess
it
> is something abnormal, can someone help me out
> Thanks,
> Subbu.
>

CPU Utilization

Hi All,

I want to keep track of the CPU utilization & number of users connected for each database on our production box. I chose to get the data from sysprocesses table from master database.

But I realised that for some reason the master..sysprocesses.CPU column stays static or just keeps on adding to existing values.

Is there any ways thru which I can clear this data ( cpu column in sysprocesses table) after I have captured it in a table ?

Any help is appreciated.

Thanks.You may be better off using perfmon with those two counters. yOu can put the output to a .csv, for later import into databases. This will not give you CPU usage per connection, however, so it is somewhat useless if you want to do chargebacks of some sort.

CPU usage by DB

I have a SQL server cluster that hosts about 40 db. The CPU utilization went
from an average of 6% to 30%. I did not add any more DBs during this
timeframe. I can see from the running processes that it is sqlservr process
using it.
How can I determine which DBs are using the most CPU?
thankstime to dust of SQLProfiler and start investigating.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Not in SQL 2000. No idea what new toys are available for 2005.
Try a PSSDiag to see what all is happening:
http://support.microsoft.com/kb/830232/en-us
--
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Hi Jason,
My understanding of your issue is that:
Your SQL Server cluster hosts about 40 databases. You found that the CPU
utilization went from an average 6% to 30%. You wanted to see which DBs are
busily using CPU.
If I have misunderstood, please let me know.
You may use SQL Profiler and PSSDiag to monitor your SQL Server
performance. However there is a direct way on this issue:
use master
Go
select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
t1.dbid=t2.dbid order by t1.dbid
Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
physical_io, memusage, status, and cmd.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles thank you for the assistance.
i ran the query. can you tell me more about what these indicators mean.
e.g. i sorted by cpu. does a large number there definitely indicate its
consuming a lot of cpu?
when i read about it in books online, it says that is the cumulitive CPU
time. to me that means it could have been running for days. so if i look at
the other columns, it show this process logged in oct 10th, over 2 weeks ago.
so is this large cpu value adding up all the time this process has used on
the cpu for the last 2 weeks?
or is there somewhere in books online that i can learn more about what i am
looking at?
"Charles Wang[MSFT]" wrote:
> Hi Jason,
> My understanding of your issue is that:
> Your SQL Server cluster hosts about 40 databases. You found that the CPU
> utilization went from an average 6% to 30%. You wanted to see which DBs are
> busily using CPU.
> If I have misunderstood, please let me know.
> You may use SQL Profiler and PSSDiag to monitor your SQL Server
> performance. However there is a direct way on this issue:
> use master
> Go
> select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
> t1.dbid=t2.dbid order by t1.dbid
> Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
> physical_io, memusage, status, and cmd.
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Hi Jason,
I am sorry for not saying clearly in my first reply. Yes, it is cumulitive
time, but it is helpful.
First, you can see the status and cmd columns to check the current
executing command and the current status of the process that the database
used. Second, you can know which database is the busiest from the
cumulitive time since it has been started; Also, you can use subtraction to
compute the CPU time cost, physical IO read and write times by running the
query twice during a time frame, then you will know which database is the
busiest during the time frame.
Hope this helpful. For the columns meaning, you can refer to sysprocesses
in SQL Server Books Online.
If you have any other questions or cnocerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

CPU usage by DB

I have a SQL server cluster that hosts about 40 db. The CPU utilization wen
t
from an average of 6% to 30%. I did not add any more DBs during this
timeframe. I can see from the running processes that it is sqlservr process
using it.
How can I determine which DBs are using the most CPU?
thankstime to dust of SQLProfiler and start investigating.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Not in SQL 2000. No idea what new toys are available for 2005.
Try a PSSDiag to see what all is happening:
http://support.microsoft.com/kb/830232/en-us
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Hi Jason,
My understanding of your issue is that:
Your SQL Server cluster hosts about 40 databases. You found that the CPU
utilization went from an average 6% to 30%. You wanted to see which DBs are
busily using CPU.
If I have misunderstood, please let me know.
You may use SQL Profiler and PSSDiag to monitor your SQL Server
performance. However there is a direct way on this issue:
use master
Go
select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
t1.dbid=t2.dbid order by t1.dbid
Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
physical_io, memusage, status, and cmd.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles thank you for the assistance.
i ran the query. can you tell me more about what these indicators mean.
e.g. i sorted by cpu. does a large number there definitely indicate its
consuming a lot of cpu?
when i read about it in books online, it says that is the cumulitive CPU
time. to me that means it could have been running for days. so if i look a
t
the other columns, it show this process logged in oct 10th, over 2 weeks ago
.
so is this large cpu value adding up all the time this process has used on
the cpu for the last 2 weeks?
or is there somewhere in books online that i can learn more about what i am
looking at?
"Charles Wang[MSFT]" wrote:

> Hi Jason,
> My understanding of your issue is that:
> Your SQL Server cluster hosts about 40 databases. You found that the CPU
> utilization went from an average 6% to 30%. You wanted to see which DBs ar
e
> busily using CPU.
> If I have misunderstood, please let me know.
> You may use SQL Profiler and PSSDiag to monitor your SQL Server
> performance. However there is a direct way on this issue:
> use master
> Go
> select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 o
n
> t1.dbid=t2.dbid order by t1.dbid
> Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
> physical_io, memusage, status, and cmd.
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Hi Jason,
I am sorry for not saying clearly in my first reply. Yes, it is cumulitive
time, but it is helpful.
First, you can see the status and cmd columns to check the current
executing command and the current status of the process that the database
used. Second, you can know which database is the busiest from the
cumulitive time since it has been started; Also, you can use subtraction to
compute the CPU time cost, physical IO read and write times by running the
query twice during a time frame, then you will know which database is the
busiest during the time frame.
Hope this helpful. For the columns meaning, you can refer to sysprocesses
in SQL Server Books Online.
If you have any other questions or cnocerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

Wednesday, March 7, 2012

cpu going high (100%) after Index is created

Hi,
Any idea why CPU is going to 100% utilization as soon as i created index
(non clustered) on a table which has indexes already on the table.
before creating index the CPU is about 10 to 20% busy, after creating index
it is going to 100% consistently for about 10 minutes then i dropped index
cpu usage came down to normal
Thanks,
SUBBU.creating index is a resource intensive process, that is one of the
reason people normally find a off peak window for reindexing.
Resources varies depends on how many rows you have of the table and how
many columns you include in the non-clustered index and what are they
(data type).
If you can provide clustered index columns (if any), non-clustered
index columns (the one you intended to put), we can find out more.
Mel|||Hi
I understand creating CPU is resource intensive. CPU is going high after
index is created not while being created.
As of now the table has 98000 rows, aprox 800 pages, 1 clustered composite,
6 non clustered composite.
Data is very static in nature
Thanks,
Subbu.
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> creating index is a resource intensive process, that is one of the
> reason people normally find a off peak window for reindexing.
> Resources varies depends on how many rows you have of the table and how
> many columns you include in the non-clustered index and what are they
> (data type).
> If you can provide clustered index columns (if any), non-clustered
> index columns (the one you intended to put), we can find out more.
> Mel
>|||You might just have gotten a bad plan. I would try it again.
--
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Hi
> I understand creating CPU is resource intensive. CPU is going high after
> index is created not while being created.
> As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> composite,
> 6 non clustered composite.
> Data is very static in nature
> Thanks,
> Subbu.
>
> "MSLam" <MelodySLam@.googlemail.com> wrote in message
> news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>> creating index is a resource intensive process, that is one of the
>> reason people normally find a off peak window for reindexing.
>> Resources varies depends on how many rows you have of the table and how
>> many columns you include in the non-clustered index and what are they
>> (data type).
>> If you can provide clustered index columns (if any), non-clustered
>> index columns (the one you intended to put), we can find out more.
>> Mel
>|||Sorry misunderstood your question in the first place.
One thing to bear in mind that putting index is NOT costless. In fact,
having more than enough indexes can cause problem, particularly if you
enviornment is OLTP. It will help for SELECT but not
UPDATE/INSERT/DELETE (as the index will need to be updated at the same
time).
My rule is always have a one good clustered index and 1-3 non-clustered
indexes should do the job.
Mel|||(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I understand creating CPU is resource intensive. CPU is going high after
> > index is created not while being created.
> >
> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > composite,
> > 6 non clustered composite.
> >
> > Data is very static in nature
> >
> > Thanks,
> > Subbu.
> >
> >
> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> creating index is a resource intensive process, that is one of the
> >> reason people normally find a off peak window for reindexing.
> >>
> >> Resources varies depends on how many rows you have of the table and how
> >> many columns you include in the non-clustered index and what are they
> >> (data type).
> >>
> >> If you can provide clustered index columns (if any), non-clustered
> >> index columns (the one you intended to put), we can find out more.
> >>
> >> Mel
> >>
> >
> >
>|||How many CPU's do you have? It's possible that parallelism is being used
suboptimally. I'd use the Profiler to find which queries are being
affected. For those, you may want to use OPTION (MAXDOP 1).
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
(BI = Before Index Created
AI = After Index created)
The query for which i have created index is executing faster AI. the query
which was going for index scan (BI) now going for index seek (AI), and
total execution time came down from 9 sec to 1 sec. But CPU is going to
100%, I am guessing does the new index made all other queries to run slow. I
dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
(the data is very static). Is SQL Server not intelligent to ignore indexes
that are not useful in plan preparation. If i create a new index it looks
like other queries which joins this table are going to wrong execution
plans. Please help.
Thanks,
Subbu
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> You might just have gotten a bad plan. I would try it again.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I understand creating CPU is resource intensive. CPU is going high after
> > index is created not while being created.
> >
> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > composite,
> > 6 non clustered composite.
> >
> > Data is very static in nature
> >
> > Thanks,
> > Subbu.
> >
> >
> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> creating index is a resource intensive process, that is one of the
> >> reason people normally find a off peak window for reindexing.
> >>
> >> Resources varies depends on how many rows you have of the table and how
> >> many columns you include in the non-clustered index and what are they
> >> (data type).
> >>
> >> If you can provide clustered index columns (if any), non-clustered
> >> index columns (the one you intended to put), we can find out more.
> >>
> >> Mel
> >>
> >
> >
>|||Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> > You might just have gotten a bad plan. I would try it again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "subbu" <subbaiahd@.hotmail.com> wrote in message
> > news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > > Hi
> > >
> > > I understand creating CPU is resource intensive. CPU is going high
after
> > > index is created not while being created.
> > >
> > > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > > composite,
> > > 6 non clustered composite.
> > >
> > > Data is very static in nature
> > >
> > > Thanks,
> > > Subbu.
> > >
> > >
> > > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> > >> creating index is a resource intensive process, that is one of the
> > >> reason people normally find a off peak window for reindexing.
> > >>
> > >> Resources varies depends on how many rows you have of the table and
how
> > >> many columns you include in the non-clustered index and what are they
> > >> (data type).
> > >>
> > >> If you can provide clustered index columns (if any), non-clustered
> > >> index columns (the one you intended to put), we can find out more.
> > >>
> > >> Mel
> > >>
> > >
> > >
> >
> >
>|||Well, it's not parallelism, then. I'd still use the profiler to figure out
where the bones are buried.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:eoh%23Ks9XGHA.4248@.TK2MSFTNGP05.phx.gbl...
Thanks for the idea, i have one cpu.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23H$8I$8XGHA.128@.TK2MSFTNGP05.phx.gbl...
> How many CPU's do you have? It's possible that parallelism is being used
> suboptimally. I'd use the Profiler to find which queries are being
> affected. For those, you may want to use OPTION (MAXDOP 1).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> > You might just have gotten a bad plan. I would try it again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "subbu" <subbaiahd@.hotmail.com> wrote in message
> > news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > > Hi
> > >
> > > I understand creating CPU is resource intensive. CPU is going high
after
> > > index is created not while being created.
> > >
> > > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> > > composite,
> > > 6 non clustered composite.
> > >
> > > Data is very static in nature
> > >
> > > Thanks,
> > > Subbu.
> > >
> > >
> > > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> > > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> > >> creating index is a resource intensive process, that is one of the
> > >> reason people normally find a off peak window for reindexing.
> > >>
> > >> Resources varies depends on how many rows you have of the table and
how
> > >> many columns you include in the non-clustered index and what are they
> > >> (data type).
> > >>
> > >> If you can provide clustered index columns (if any), non-clustered
> > >> index columns (the one you intended to put), we can find out more.
> > >>
> > >> Mel
> > >>
> > >
> > >
> >
> >
>|||If you query originally took 9 seconds and now takes 1 then you must be
returning a lot of rows. The CPU going to 100% is probably just a sign that
it is now able to process more efficiently and thus faster. If no one lese
is on the server and you issue the query and the CPU spikes to 100% for 1
second and then drops to 0 everything is OK.
--
Andrew J. Kelly SQL MVP
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> (BI = Before Index Created
> AI = After Index created)
>
> The query for which i have created index is executing faster AI. the query
> which was going for index scan (BI) now going for index seek (AI), and
> total execution time came down from 9 sec to 1 sec. But CPU is going to
> 100%, I am guessing does the new index made all other queries to run slow.
> I
> dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> (the data is very static). Is SQL Server not intelligent to ignore indexes
> that are not useful in plan preparation. If i create a new index it looks
> like other queries which joins this table are going to wrong execution
> plans. Please help.
> Thanks,
> Subbu
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
>> You might just have gotten a bad plan. I would try it again.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "subbu" <subbaiahd@.hotmail.com> wrote in message
>> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
>> > Hi
>> >
>> > I understand creating CPU is resource intensive. CPU is going high
>> > after
>> > index is created not while being created.
>> >
>> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
>> > composite,
>> > 6 non clustered composite.
>> >
>> > Data is very static in nature
>> >
>> > Thanks,
>> > Subbu.
>> >
>> >
>> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
>> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
>> >> creating index is a resource intensive process, that is one of the
>> >> reason people normally find a off peak window for reindexing.
>> >>
>> >> Resources varies depends on how many rows you have of the table and
>> >> how
>> >> many columns you include in the non-clustered index and what are they
>> >> (data type).
>> >>
>> >> If you can provide clustered index columns (if any), non-clustered
>> >> index columns (the one you intended to put), we can find out more.
>> >>
>> >> Mel
>> >>
>> >
>> >
>>
>|||Sorry for responding late.
Query is returning only few rows around 25. After query executed even then
cpu is not coming down, query executed in one second cpu is 100% for almost
10 minutes then i dropped new indexes created , and immeadiately cpu came
down to normal (20% ).
Thanks,
Subbu.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJF9hdAYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> If you query originally took 9 seconds and now takes 1 then you must be
> returning a lot of rows. The CPU going to 100% is probably just a sign
that
> it is now able to process more efficiently and thus faster. If no one lese
> is on the server and you issue the query and the CPU spikes to 100% for 1
> second and then drops to 0 everything is OK.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> >
> > (BI = Before Index Created
> > AI = After Index created)
> >
> >
> > The query for which i have created index is executing faster AI. the
query
> > which was going for index scan (BI) now going for index seek (AI), and
> > total execution time came down from 9 sec to 1 sec. But CPU is going to
> > 100%, I am guessing does the new index made all other queries to run
slow.
> > I
> > dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> > (the data is very static). Is SQL Server not intelligent to ignore
indexes
> > that are not useful in plan preparation. If i create a new index it
looks
> > like other queries which joins this table are going to wrong execution
> > plans. Please help.
> >
> > Thanks,
> > Subbu
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> >> You might just have gotten a bad plan. I would try it again.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "subbu" <subbaiahd@.hotmail.com> wrote in message
> >> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> >
> >> > I understand creating CPU is resource intensive. CPU is going high
> >> > after
> >> > index is created not while being created.
> >> >
> >> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> >> > composite,
> >> > 6 non clustered composite.
> >> >
> >> > Data is very static in nature
> >> >
> >> > Thanks,
> >> > Subbu.
> >> >
> >> >
> >> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> >> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> >> creating index is a resource intensive process, that is one of the
> >> >> reason people normally find a off peak window for reindexing.
> >> >>
> >> >> Resources varies depends on how many rows you have of the table and
> >> >> how
> >> >> many columns you include in the non-clustered index and what are
they
> >> >> (data type).
> >> >>
> >> >> If you can provide clustered index columns (if any), non-clustered
> >> >> index columns (the one you intended to put), we can find out more.
> >> >>
> >> >> Mel
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Again, use the profiler to determine the ling-running queries, as well as
those that take a lot of CPU.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:OBoqVgiYGHA.3328@.TK2MSFTNGP02.phx.gbl...
Sorry for responding late.
Query is returning only few rows around 25. After query executed even then
cpu is not coming down, query executed in one second cpu is 100% for almost
10 minutes then i dropped new indexes created , and immeadiately cpu came
down to normal (20% ).
Thanks,
Subbu.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJF9hdAYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> If you query originally took 9 seconds and now takes 1 then you must be
> returning a lot of rows. The CPU going to 100% is probably just a sign
that
> it is now able to process more efficiently and thus faster. If no one lese
> is on the server and you issue the query and the CPU spikes to 100% for 1
> second and then drops to 0 everything is OK.
> --
> Andrew J. Kelly SQL MVP
>
> "subbu" <subbaiahd@.hotmail.com> wrote in message
> news:uLyApr8XGHA.4920@.TK2MSFTNGP02.phx.gbl...
> >
> > (BI = Before Index Created
> > AI = After Index created)
> >
> >
> > The query for which i have created index is executing faster AI. the
query
> > which was going for index scan (BI) now going for index seek (AI), and
> > total execution time came down from 9 sec to 1 sec. But CPU is going to
> > 100%, I am guessing does the new index made all other queries to run
slow.
> > I
> > dont worry about INSERT/DELETE/UPDATE, I need to tune SELECT stmts only
> > (the data is very static). Is SQL Server not intelligent to ignore
indexes
> > that are not useful in plan preparation. If i create a new index it
looks
> > like other queries which joins this table are going to wrong execution
> > plans. Please help.
> >
> > Thanks,
> > Subbu
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OltKJu1XGHA.4652@.TK2MSFTNGP04.phx.gbl...
> >> You might just have gotten a bad plan. I would try it again.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "subbu" <subbaiahd@.hotmail.com> wrote in message
> >> news:%235Pf63zXGHA.3516@.TK2MSFTNGP03.phx.gbl...
> >> > Hi
> >> >
> >> > I understand creating CPU is resource intensive. CPU is going high
> >> > after
> >> > index is created not while being created.
> >> >
> >> > As of now the table has 98000 rows, aprox 800 pages, 1 clustered
> >> > composite,
> >> > 6 non clustered composite.
> >> >
> >> > Data is very static in nature
> >> >
> >> > Thanks,
> >> > Subbu.
> >> >
> >> >
> >> > "MSLam" <MelodySLam@.googlemail.com> wrote in message
> >> > news:1144960243.264788.102540@.z34g2000cwc.googlegroups.com...
> >> >> creating index is a resource intensive process, that is one of the
> >> >> reason people normally find a off peak window for reindexing.
> >> >>
> >> >> Resources varies depends on how many rows you have of the table and
> >> >> how
> >> >> many columns you include in the non-clustered index and what are
they
> >> >> (data type).
> >> >>
> >> >> If you can provide clustered index columns (if any), non-clustered
> >> >> index columns (the one you intended to put), we can find out more.
> >> >>
> >> >> Mel
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Thursday, March 8, 2012

CPU utilization not even

CPU utilization not even

CPU utilization not even

CPU Utilization

CPU usage by DB

CPU usage by DB

Wednesday, March 7, 2012

cpu going high (100%) after Index is created

cpu going high (100%) after Index is created

CREATE DATABASE failed

Blog Archive

About Me