happy friday...
my table has 200,000 + records and I want to see the particular record which I think it is causing the problem.
How could i query 195,203rd record?
thank you, yanorooWhat problem is it causing? How did you figure out the number to begin with?|||What problem is it causing? How did you figure out the number to begin with?
oh, the record number is just an example.
actual case is -I ran dts package to import data from txt file to sql table.
The dts truncated the table and imported the records correctly (218503 record) but I can't find the data from 208775th record on the table...
I want to see the what was populated into the table from 208775th record.
And strange thing is the table has 237043 record (which is more than input)|||are you getting 237043 by doing a count(*) on the table?|||are you getting 237043 by doing a count(*) on the table?
yes, it is by count(*) on the table.|||If you are just debugging the process, then temporarily create an incrementing identity column on the table.|||If you are just debugging the process, then temporarily create an incrementing identity column on the table.
Hello,
Found out the problem was DTS prod installation error.
Thank you anyway for your suggestion.
Yanoroo|||i suggest in a situation like this to create a staging table when you transfer large amounts of data to an sql server. this way you can set the error count high and allow sql server to leave the rows causing the error in the staging table while you move the clean data to the final destination. you can then have the dts send you an email notifying you of the prob and you can clean those rows in the morning.
go to www.SQLDts.com|||I assume there is an unique field named pid in your table and you are looking for a particular record of pid(in this case 195,203rd).You can try this query--
select * from mytable where pid not in
(select top 195,202 pid from mytable)
If you have any other type of requirement that can be solved too.
Subhasish
Showing posts with label particular. Show all posts
Showing posts with label particular. Show all posts
Wednesday, March 28, 2012
Query -- 11 seconds in SSMS but times out after 30 minutes in
We're having a weird problem -- a particular report will time out
after 30 minutes running in Reporting Services. However, if we grab
the query from Profiler and run it in SSMS, it runs in about 11
seconds. The profiler trace shows an error of 2 - Abort.
The query itself is found below -- I won't include DDL because each of
the items in the FROM clause is a view that is very long.
Any explanation as to why the query would take so long when running
from reporting services?
Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
3790). Running on Windows Server 2003 Enterprise x64 SP2.
There are some things I'd like to clean up after seeing it, but
nonetheless it still runs pretty quick in SSMS.
SELECT
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
WHEN 3 THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
, e.SHORT_NAME, e.SSN
, SUM(bck.COST)
, e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
FROM
ahr_custom.sls.nocorp_ckreg ck
inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no = ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no = bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
inner join ahr_custom.sls.nocorp_beneplan b on b.company = bck.company and b.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_employee e on e.company = ck.company and e.emp_no = p.emp_no
inner join ahr_custom.sls.nocorp_empbene eb on bck.company = eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_clients c on ck.company = c.company and c.client_no = e.client_no1
WHERE
bck.PLAN_NO IN (20676)
AND p.COMPANY IN ('OK','CA','C2','CO','TX')
and Left(CONVERT(varchar,ck.ck_date,120),10)
between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
+cast(month(getdate()-20) as varchar(2)),2))+ '-01'
and LEFT( CONVERT(varchar,
ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
GROUP BY
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
, e.SHORT_NAME, e.SSN
,e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
LEFT( CONVERT(varchar,e.terminated, 120), 10) endHow many rows of data is returned?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:ac49df7d-bd90-4f6d-b7c8-44012e8e9bb3@.w56g2000hsf.googlegroups.com...
> We're having a weird problem -- a particular report will time out
> after 30 minutes running in Reporting Services. However, if we grab
> the query from Profiler and run it in SSMS, it runs in about 11
> seconds. The profiler trace shows an error of 2 - Abort.
> The query itself is found below -- I won't include DDL because each of
> the items in the FROM clause is a view that is very long.
> Any explanation as to why the query would take so long when running
> from reporting services?
> Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
> 3790). Running on Windows Server 2003 Enterprise x64 SP2.
> There are some things I'd like to clean up after seeing it, but
> nonetheless it still runs pretty quick in SSMS.
> SELECT
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
> WHEN 3 THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
> b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
> , e.SHORT_NAME, e.SSN
> , SUM(bck.COST)
> , e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
> else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
> FROM
> ahr_custom.sls.nocorp_ckreg ck
> inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no => ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
> inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no => bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
> inner join ahr_custom.sls.nocorp_beneplan b on b.company => bck.company and b.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_employee e on e.company => ck.company and e.emp_no = p.emp_no
> inner join ahr_custom.sls.nocorp_empbene eb on bck.company => eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_clients c on ck.company => c.company and c.client_no = e.client_no1
> WHERE
> bck.PLAN_NO IN (20676)
> AND p.COMPANY IN ('OK','CA','C2','CO','TX')
> and Left(CONVERT(varchar,ck.ck_date,120),10)
> between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
> +cast(month(getdate()-20) as varchar(2)),2))+ '-01'
> and LEFT( CONVERT(varchar,
> ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
> GROUP BY
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
> THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
> , e.SHORT_NAME, e.SSN
> ,e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
> LEFT( CONVERT(varchar,e.terminated, 120), 10) end|||On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How many rows of data is returned?
502 rows|||OK, number of rows is not a problem. My guess is that you are having an
issue with the query plan being different.
First, can you put this in a stored procedure and then call that from both
places and see if that makes a difference?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:90230c5c-f7c7-4592-8b44-83ce3a381729@.s12g2000prg.googlegroups.com...
> On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How many rows of data is returned?
> 502 rows|||> First, can you put this in a stored procedure and then call that from both
> places and see if that makes a difference?
Wow -- good suggestion! That seemed to fix it.
Any ideas why in the world would a different query plan be generated
from Reporting Services than from SSMS? It seems like either A)
Profiler isn't giving me the right query that's being executed via
Reporting Services or B) different query plans are being generated for
the same SQL statement depending on the calling application. Neither
of those make any sense to me.
Thanks again for your help.|||I have seen this with other people but with stored procedures not SQL. If a
stored procedure acts differently then you can add a With Recompile to fix
the problem. I really don't understand how the same SQL can act different
but that is what I thought was happening to you.
Bruce
<stephen.lee.moore@.gmail.com> wrote in message
news:2cd5adad-6a86-414b-9840-2b3b05e58c31@.j44g2000hsj.googlegroups.com...
>> First, can you put this in a stored procedure and then call that from
>> both
>> places and see if that makes a difference?
> Wow -- good suggestion! That seemed to fix it.
> Any ideas why in the world would a different query plan be generated
> from Reporting Services than from SSMS? It seems like either A)
> Profiler isn't giving me the right query that's being executed via
> Reporting Services or B) different query plans are being generated for
> the same SQL statement depending on the calling application. Neither
> of those make any sense to me.
> Thanks again for your help.
after 30 minutes running in Reporting Services. However, if we grab
the query from Profiler and run it in SSMS, it runs in about 11
seconds. The profiler trace shows an error of 2 - Abort.
The query itself is found below -- I won't include DDL because each of
the items in the FROM clause is a view that is very long.
Any explanation as to why the query would take so long when running
from reporting services?
Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
3790). Running on Windows Server 2003 Enterprise x64 SP2.
There are some things I'd like to clean up after seeing it, but
nonetheless it still runs pretty quick in SSMS.
SELECT
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
WHEN 3 THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
, e.SHORT_NAME, e.SSN
, SUM(bck.COST)
, e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
FROM
ahr_custom.sls.nocorp_ckreg ck
inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no = ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no = bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
inner join ahr_custom.sls.nocorp_beneplan b on b.company = bck.company and b.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_employee e on e.company = ck.company and e.emp_no = p.emp_no
inner join ahr_custom.sls.nocorp_empbene eb on bck.company = eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
inner join ahr_custom.sls.nocorp_clients c on ck.company = c.company and c.client_no = e.client_no1
WHERE
bck.PLAN_NO IN (20676)
AND p.COMPANY IN ('OK','CA','C2','CO','TX')
and Left(CONVERT(varchar,ck.ck_date,120),10)
between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
+cast(month(getdate()-20) as varchar(2)),2))+ '-01'
and LEFT( CONVERT(varchar,
ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
GROUP BY
e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
THEN b.desc_3
WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
, e.SHORT_NAME, e.SSN
,e.STATUS
,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
LEFT( CONVERT(varchar,e.terminated, 120), 10) endHow many rows of data is returned?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:ac49df7d-bd90-4f6d-b7c8-44012e8e9bb3@.w56g2000hsf.googlegroups.com...
> We're having a weird problem -- a particular report will time out
> after 30 minutes running in Reporting Services. However, if we grab
> the query from Profiler and run it in SSMS, it runs in about 11
> seconds. The profiler trace shows an error of 2 - Abort.
> The query itself is found below -- I won't include DDL because each of
> the items in the FROM clause is a view that is very long.
> Any explanation as to why the query would take so long when running
> from reporting services?
> Our environment -- SQL 2005 Standard x64, SP2 (9.00.3054.00 / Build
> 3790). Running on Windows Server 2003 Enterprise x64 SP2.
> There are some things I'd like to clean up after seeing it, but
> nonetheless it still runs pretty quick in SSMS.
> SELECT
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2
> WHEN 3 THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN
> b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END AS Plan_Option
> , e.SHORT_NAME, e.SSN
> , SUM(bck.COST)
> , e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' '
> else LEFT( CONVERT(varchar,e.terminated, 120), 10) end as term_date
> FROM
> ahr_custom.sls.nocorp_ckreg ck
> inner join ahr_custom.sls.nocorp_prckreg p ON p.ck_no => ck.ck_no AND p.ck_style = ck.ck_style AND p.company = ck.company
> inner join ahr_custom.sls.nocorp_beckreg bck ON p.ck_no => bck.ck_no AND p.ck_style = bck.ck_style AND p.company = bck.company
> inner join ahr_custom.sls.nocorp_beneplan b on b.company => bck.company and b.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_employee e on e.company => ck.company and e.emp_no = p.emp_no
> inner join ahr_custom.sls.nocorp_empbene eb on bck.company => eb.company and e.emp_no = eb.emp_no and eb.plan_no = bck.plan_no
> inner join ahr_custom.sls.nocorp_clients c on ck.company => c.company and c.client_no = e.client_no1
> WHERE
> bck.PLAN_NO IN (20676)
> AND p.COMPANY IN ('OK','CA','C2','CO','TX')
> and Left(CONVERT(varchar,ck.ck_date,120),10)
> between cast(year(getdate()-20) as varchar(4)) +'-'+ (right( '0'
> +cast(month(getdate()-20) as varchar(2)),2))+ '-01'
> and LEFT( CONVERT(varchar,
> ahr_custom.dbo.lastdayofmonth(getdate()-20) , 120), 10)
> GROUP BY
> e.COMPANY, b.PLAN_NO, b.DESC_, c.CLIENT, c.CLIENT_NO
> ,CASE eb.option_ WHEN 1 THEN b.desc_1 WHEN 2 THEN b.desc_2 WHEN 3
> THEN b.desc_3
> WHEN 4 THEN b.desc_4 WHEN 5 THEN b.desc_5 WHEN 6 THEN b.desc_6
> WHEN 7 THEN b.desc_7 ELSE b.desc_8 END
> , e.SHORT_NAME, e.SSN
> ,e.STATUS
> ,case when e.terminated ='1753-01-01 00:00:00.000' then ' ' else
> LEFT( CONVERT(varchar,e.terminated, 120), 10) end|||On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How many rows of data is returned?
502 rows|||OK, number of rows is not a problem. My guess is that you are having an
issue with the query plan being different.
First, can you put this in a stored procedure and then call that from both
places and see if that makes a difference?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<stephen.lee.moore@.gmail.com> wrote in message
news:90230c5c-f7c7-4592-8b44-83ce3a381729@.s12g2000prg.googlegroups.com...
> On Dec 4, 11:07 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How many rows of data is returned?
> 502 rows|||> First, can you put this in a stored procedure and then call that from both
> places and see if that makes a difference?
Wow -- good suggestion! That seemed to fix it.
Any ideas why in the world would a different query plan be generated
from Reporting Services than from SSMS? It seems like either A)
Profiler isn't giving me the right query that's being executed via
Reporting Services or B) different query plans are being generated for
the same SQL statement depending on the calling application. Neither
of those make any sense to me.
Thanks again for your help.|||I have seen this with other people but with stored procedures not SQL. If a
stored procedure acts differently then you can add a With Recompile to fix
the problem. I really don't understand how the same SQL can act different
but that is what I thought was happening to you.
Bruce
<stephen.lee.moore@.gmail.com> wrote in message
news:2cd5adad-6a86-414b-9840-2b3b05e58c31@.j44g2000hsj.googlegroups.com...
>> First, can you put this in a stored procedure and then call that from
>> both
>> places and see if that makes a difference?
> Wow -- good suggestion! That seemed to fix it.
> Any ideas why in the world would a different query plan be generated
> from Reporting Services than from SSMS? It seems like either A)
> Profiler isn't giving me the right query that's being executed via
> Reporting Services or B) different query plans are being generated for
> the same SQL statement depending on the calling application. Neither
> of those make any sense to me.
> Thanks again for your help.
Monday, March 26, 2012
QUery
For retreiving a particular result I can write a query in this way-
Select t1.person_id,t2.age from table1 t1,table2 t2
where t1.person_id=t2.person_id
Or
I can write the same query in a different manner like this-
Select t1.person_id,t2.age from table1 t1
inner join table2 t2
on t1.person_id=t2.person_id
I want to know is there any particular effect between these two processes?Which one is supposed to follow?
SubhasishThere is no difference in effects for the both, but the 'inner join' statement complies with the latest ANSI standard and the other one is the old way.|||The second version is the ANSI way of writing a join. It's also far easier to understand IMHO.
To make that query even simpler to follow, you could use a USING clause:
SELECT
t1.person_id
, t2.age
FROM
table1 t1
INNER JOIN
table2 t2
USING ( person_id )
Have a look at this ANSI join article at Database Journal (http://www.databasejournal.com/features/oracle/article.php/2209301).
Hope this helps,
Matt. :)|||subhasishray,
the reason it make no difference for a simple query such as yours is because MSSQL Server will change WHERE logic to JOIN logic prior to processing whenever possible. For complex queries the optimizer may not be able to do this, and the WHERE logic can affect performance.
use JOIN logic whenever you can. it logically separates table linking from query criteria and filters.
blindman
Select t1.person_id,t2.age from table1 t1,table2 t2
where t1.person_id=t2.person_id
Or
I can write the same query in a different manner like this-
Select t1.person_id,t2.age from table1 t1
inner join table2 t2
on t1.person_id=t2.person_id
I want to know is there any particular effect between these two processes?Which one is supposed to follow?
SubhasishThere is no difference in effects for the both, but the 'inner join' statement complies with the latest ANSI standard and the other one is the old way.|||The second version is the ANSI way of writing a join. It's also far easier to understand IMHO.
To make that query even simpler to follow, you could use a USING clause:
SELECT
t1.person_id
, t2.age
FROM
table1 t1
INNER JOIN
table2 t2
USING ( person_id )
Have a look at this ANSI join article at Database Journal (http://www.databasejournal.com/features/oracle/article.php/2209301).
Hope this helps,
Matt. :)|||subhasishray,
the reason it make no difference for a simple query such as yours is because MSSQL Server will change WHERE logic to JOIN logic prior to processing whenever possible. For complex queries the optimizer may not be able to do this, and the WHERE logic can affect performance.
use JOIN logic whenever you can. it logically separates table linking from query criteria and filters.
blindman
Wednesday, March 21, 2012
queries running very slow on a particular table
I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/
queries running very slow on a particular table
I have a web application that hits this database 24/7. I have an orders tabl
e
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders tab
le
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the othe
r
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still n
o
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/sql
e
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders tab
le
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the othe
r
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still n
o
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/sql
queries running very slow on a particular table
I have a web application that hits this database 24/7. I have an orders table
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.
Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/
and any query that I run on this table is very slow. A simple select query
also runs very slow. This was working fine until this morning. All the other
tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
good. Please advise.
Have you looked at what the query plan shows for the query on the orders
table? It could be the statistics need to be updated, however the dbcc
dbreindex should have handled that. You may need to run a profiler to
see if the problem is with the table access or perhaps temp tables.
Shahryar
Beginner wrote:
>I have a web application that hits this database 24/7. I have an orders table
>and any query that I run on this table is very slow. A simple select query
>also runs very slow. This was working fine until this morning. All the other
>tables work fine. I ran dbcc showcontig on then ran dbcc dbreindex. Still no
>good. Please advise.
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Any blocking going on?
run sp_who2 and look for the BlkBy column
http://sqlservercode.blogspot.com/
Subscribe to:
Posts (Atom)