plz help me out

we have set up merge rep and it is working fine. i have setup alerts if rep
fails.
recently i am not getting these alerts what would be the reason
how to configure alerts
Raghu,
please can you tell us which alerts are firing and any error messages
received from the merge agent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||hai sir
right now i am not getting any kind of alerts and the message
is
the sql server agent mail session not running
"Paul Ibison" wrote:

> Raghu,
> please can you tell us which alerts are firing and any error messages
> received from the merge agent.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Raghu,
have a look at these links for some more info on setting up mail/sql
integration:
http://support.microsoft.com/default...315886&sd=tech
http://support.microsoft.com/default...263556&sd=tech
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||hai paul sir
even though i read the articles i did not solved the error
plz tell me other way
"Paul Ibison" wrote:

> Raghu,
> have a look at these links for some more info on setting up mail/sql
> integration:
> http://support.microsoft.com/default...315886&sd=tech
> http://support.microsoft.com/default...263556&sd=tech
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Raghu,
to be honest, for mail setup you're best off posting in the sqlserver.server
group. I'll chip in if there's no more specialized responses, but you're
likely to get more responses there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Please test search engine

Hie there.
I'm a little frenchie (froggie ? lol) and I'm just working on a Web
Search engine which I would appreciate you to test...
Here the URL
http://erwann.conan.free.fr
"Insertion d'un site" means "insert a web site"
"Recherche d'un site" means "look for a web site"
Thanks a lotWhy would primarily English-speaking (or at least EFL) SQL Server
programmers have any interest in "testing" a French search engine? Did you
also post this request to the BMW and Debian newsgroups?
"IMMO" <erwanconan83@.hotmail.com> wrote in message
news:1131583585.955363.324020@.o13g2000cwo.googlegroups.com...
> Hie there.
> I'm a little frenchie (froggie ? lol) and I'm just working on a Web
> Search engine which I would appreciate you to test...
> Here the URL
> http://erwann.conan.free.fr
> "Insertion d'un site" means "insert a web site"
> "Recherche d'un site" means "look for a web site"
> Thanks a lot
>|||Because we Americans and Brits have more experience building things... and
breaking them. ;-P
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23fsTQsZ5FHA.156@.TK2MSFTNGP15.phx.gbl...
> Why would primarily English-speaking (or at least EFL) SQL Server
> programmers have any interest in "testing" a French search engine? Did
> you also post this request to the BMW and Debian newsgroups?
>
>
>
> "IMMO" <erwanconan83@.hotmail.com> wrote in message
> news:1131583585.955363.324020@.o13g2000cwo.googlegroups.com...
>|||Good thing us Canadians are there to fix the things that you guys break. :-)
"JT" <someone@.microsoft.com> wrote in message
news:uvvEn6j5FHA.2888@.tk2msftngp13.phx.gbl...
> Because we Americans and Brits have more experience building things... and
> breaking them. ;-P
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23fsTQsZ5FHA.156@.TK2MSFTNGP15.phx.gbl...
>

Please suggest type Of Replication suitable in this scenario !

Hi All:
I have 2 sites, one here in India & one at Montreal working on same
database, hosted here (India), using ASP Application.
The link is dedicated over WAN. Now I want both sites to access their own DB
at their local ends (structure being same) & then replicate/snycronize both
Databases periodically, say every 60 mins or in worst case once every day.
Both sites receive customer inbound calls & people take orders, troubleshoot
product. The DB size is 3 GB & growing, though slowly.
What is best type of replication which i can use here?
Please suggest!
Thanks,
Anukul
anukuls@.yahoo.com
If your schema is static, and your users will never be accessing these
databases at the same time or if you can minimize conflicts using
partitioning you could use bi-directional transactional replication.
Otherwise you should look at merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> Hi All:
> I have 2 sites, one here in India & one at Montreal working on same
> database, hosted here (India), using ASP Application.
> The link is dedicated over WAN. Now I want both sites to access their own
DB
> at their local ends (structure being same) & then replicate/snycronize
both
> Databases periodically, say every 60 mins or in worst case once every day.
> Both sites receive customer inbound calls & people take orders,
troubleshoot
> product. The DB size is 3 GB & growing, though slowly.
> What is best type of replication which i can use here?
> Please suggest!
> Thanks,
> Anukul
> anukuls@.yahoo.com
|||Thanks Hilary!
Yes the Database structure is static, but may change once every 1 month.
Also, users will be accessing the DB simultaneously at both ends, as Inbound
customer calls will be received at both sites, so changes relating to a
customer's address, name, order may get updated. I am over looking the
scenario where same customer calls at same time at both sites, but the same
customer's data may be worked on at both sites at same time. My ASP
application will change as it uses identities right now, but will I will
create site specific ids.
Please suggest.
Thanks,
Anukul
"Hilary Cotter" wrote:

> If your schema is static, and your users will never be accessing these
> databases at the same time or if you can minimize conflicts using
> partitioning you could use bi-directional transactional replication.
> Otherwise you should look at merge replication.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> DB
> both
> troubleshoot
>
>
|||merge replication will probably work best for you then.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Thanks Hilary!
> Yes the Database structure is static, but may change once every 1 month.
> Also, users will be accessing the DB simultaneously at both ends, as
Inbound
> customer calls will be received at both sites, so changes relating to a
> customer's address, name, order may get updated. I am over looking the
> scenario where same customer calls at same time at both sites, but the
same[vbcol=seagreen]
> customer's data may be worked on at both sites at same time. My ASP
> application will change as it uses identities right now, but will I will
> create site specific ids.
> Please suggest.
> Thanks,
> Anukul
> "Hilary Cotter" wrote:
own[vbcol=seagreen]
day.[vbcol=seagreen]
|||Thanks Again!
Is there a Book which you can recommend to setup Merge Replication?
Are there any 3rd party tools for Conflict Resolution available?
Please guide.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> merge replication will probably work best for you then.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Inbound
> same
their[vbcol=seagreen]
> own
replicate/snycronize[vbcol=seagreen]
every
> day.
>
|||Well I am working on a merge book. There are no third party tools I know of.
The default conflict resolvers which ship with SQL Server are pretty good.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks Again!
> Is there a Book which you can recommend to setup Merge Replication?
> Are there any 3rd party tools for Conflict Resolution available?
> Please guide.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...
month.[vbcol=seagreen]
a[vbcol=seagreen]
will[vbcol=seagreen]
these[vbcol=seagreen]
replication.[vbcol=seagreen]
same
> their
> replicate/snycronize
> every
>
|||Hi Hilary:
Just another query:
As I mentioned , what would be the best way of adding / dropping columns to
an existing Database, which is published for replication (merge
replication)?
Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other ways
to it ? Even datatype changes or size changes.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Well I am working on a merge book. There are no third party tools I know
of.[vbcol=seagreen]
> The default conflict resolvers which ship with SQL Server are pretty good.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <anukuls@.yahoo.com> wrote in message
> news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> month.
to[vbcol=seagreen]
> a
the[vbcol=seagreen]
the
> will
> these
> replication.
> same
>
|||sp_repladdcolumn and sp_repldropcolumn are the best ways.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:O59cEGA9EHA.3640@.tk2msftngp13.phx.gbl...
> Hi Hilary:
> Just another query:
> As I mentioned , what would be the best way of adding / dropping columns
to
> an existing Database, which is published for replication (merge
> replication)?
> Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other
ways[vbcol=seagreen]
> to it ? Even datatype changes or size changes.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> of.
good.[vbcol=seagreen]
as[vbcol=seagreen]
> to
> the
> the
ASP[vbcol=seagreen]
I[vbcol=seagreen]
using[vbcol=seagreen]
access[vbcol=seagreen]
once[vbcol=seagreen]
orders,
>
|||Also, have a look at an article on
www.replicationanswers.com for methods of changing an
existing datatype, and Kestutis's post from yesterday.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Please suggest me any helpful article on Sql Server 2005 Reporting service

I am working on yukon.

can anyone suggest me any helpful material about the same

Sorry for simple query.

Aa lot of material was found on msdn.Microsoft.com

|||

same can be found on http://asp.net

or by searching on google.com

Please please answer my question!

Hi,

I'm working on both VWDEE and VBEE and, in each, when I try to add a database diagram I get the following error message'Invalid Class String'(after the 'Do you want to create required objects' prompt).

Can someone postulate a reason? If I don't get an answer i'll be pretty much dead in the water

Thanks in advance of your help.

I've seen this issue crop up on SQL Server forums and the advice tends to be to reinstall. I had a quick search to refresh my memory - see thishttp://www.dbforums.com/archive/index.php/t-673390.html

May not help. I'd advise when you're posting to put a more descriptive subject than "please answer" as you're more likely to find someone who's had the same problem. Good luck!

PLEASE HELP! Unable to connect to SQL Database from Report Man

Hi,
I am working with SQL Reporting services 2000... The report is unable to
connect to a remote datasource as I try to preview it. The database uses both
SQL and Windows Auth.
1. Created a shared datasource in my project
2. Deployed it on the server. Used OverwriteDataSource = false
3. Create a new shared datasource on the server. Gave a connection string
with no username and password.
4. Selected "Credentials stored securely in the report server" and then
entered the SQL server login and passoword there.
5. Changed the report properties in the manager to use this datasource.
6. While trying to "View" via report manager, I am getting the error message
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
Get Online Help
SQL Server does not exist or access denied.
CAN SOMEONE PLEASE HELP ME!!!!!!I too had this problem. The way that I fixed it was by making sure that I
could connect to the SQL server in question from the server that hosted the
reporting services website. It was the that I realized two very important
things.
1. The user profile that reporting services uses to run as a service needs
SQL server access.
2. The user profile that I entered as the login for the report needed to be
qualified with a domain name if it was a windows account.
These may not be your problem, but they were mine. I hope this helps
"PV" wrote:
> Hi,
> I am working with SQL Reporting services 2000... The report is unable to
> connect to a remote datasource as I try to preview it. The database uses both
> SQL and Windows Auth.
> 1. Created a shared datasource in my project
> 2. Deployed it on the server. Used OverwriteDataSource = false
> 3. Create a new shared datasource on the server. Gave a connection string
> with no username and password.
> 4. Selected "Credentials stored securely in the report server" and then
> entered the SQL server login and passoword there.
> 5. Changed the report properties in the manager to use this datasource.
> 6. While trying to "View" via report manager, I am getting the error message
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
> Get Online Help
> SQL Server does not exist or access denied.
>
> CAN SOMEONE PLEASE HELP ME!!!!!!|||I confirmed this... Found out that I could connect from the Reporting
services machine to my SQL Server using the same SQL credentials...
Also gave access to the DB to the user that the Report Manager runs under...
But this still occurs.
"Nikolai Sonin" wrote:
> I too had this problem. The way that I fixed it was by making sure that I
> could connect to the SQL server in question from the server that hosted the
> reporting services website. It was the that I realized two very important
> things.
> 1. The user profile that reporting services uses to run as a service needs
> SQL server access.
> 2. The user profile that I entered as the login for the report needed to be
> qualified with a domain name if it was a windows account.
> These may not be your problem, but they were mine. I hope this helps
>
> "PV" wrote:
> > Hi,
> > I am working with SQL Reporting services 2000... The report is unable to
> > connect to a remote datasource as I try to preview it. The database uses both
> > SQL and Windows Auth.
> >
> > 1. Created a shared datasource in my project
> > 2. Deployed it on the server. Used OverwriteDataSource = false
> > 3. Create a new shared datasource on the server. Gave a connection string
> > with no username and password.
> > 4. Selected "Credentials stored securely in the report server" and then
> > entered the SQL server login and passoword there.
> > 5. Changed the report properties in the manager to use this datasource.
> > 6. While trying to "View" via report manager, I am getting the error message
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
> > Get Online Help
> > SQL Server does not exist or access denied.
> >
> >
> > CAN SOMEONE PLEASE HELP ME!!!!!!|||I'm having a very similar problem, but I have two reports using the
datascource and rendering no problem and a third that gets the
rsErrorOpeningConnection error. This report previews just fine in VS, but
when not when deployed to the server. Anyone know if RS is does some sort of
credential caching?
"PV" wrote:
> I confirmed this... Found out that I could connect from the Reporting
> services machine to my SQL Server using the same SQL credentials...
> Also gave access to the DB to the user that the Report Manager runs under...
> But this still occurs.
>
> "Nikolai Sonin" wrote:
> > I too had this problem. The way that I fixed it was by making sure that I
> > could connect to the SQL server in question from the server that hosted the
> > reporting services website. It was the that I realized two very important
> > things.
> > 1. The user profile that reporting services uses to run as a service needs
> > SQL server access.
> > 2. The user profile that I entered as the login for the report needed to be
> > qualified with a domain name if it was a windows account.
> > These may not be your problem, but they were mine. I hope this helps
> >
> >
> > "PV" wrote:
> >
> > > Hi,
> > > I am working with SQL Reporting services 2000... The report is unable to
> > > connect to a remote datasource as I try to preview it. The database uses both
> > > SQL and Windows Auth.
> > >
> > > 1. Created a shared datasource in my project
> > > 2. Deployed it on the server. Used OverwriteDataSource = false
> > > 3. Create a new shared datasource on the server. Gave a connection string
> > > with no username and password.
> > > 4. Selected "Credentials stored securely in the report server" and then
> > > entered the SQL server login and passoword there.
> > > 5. Changed the report properties in the manager to use this datasource.
> > > 6. While trying to "View" via report manager, I am getting the error message
> > >
> > > An error has occurred during report processing. (rsProcessingAborted) Get
> > > Online Help
> > > Cannot create a connection to data source 'xxxx'. (rsErrorOpeningConnection)
> > > Get Online Help
> > > SQL Server does not exist or access denied.
> > >
> > >
> > > CAN SOMEONE PLEASE HELP ME!!!!!!|||There is no credential caching between data sources. Often this is some sort
of network name resolution issue.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leef" <Leef@.discussions.microsoft.com> wrote in message
news:D69B9ED5-00A6-4ECF-9501-C269709E90B8@.microsoft.com...
> I'm having a very similar problem, but I have two reports using the
> datascource and rendering no problem and a third that gets the
> rsErrorOpeningConnection error. This report previews just fine in VS, but
> when not when deployed to the server. Anyone know if RS is does some sort
> of
> credential caching?
> "PV" wrote:
>> I confirmed this... Found out that I could connect from the Reporting
>> services machine to my SQL Server using the same SQL credentials...
>> Also gave access to the DB to the user that the Report Manager runs
>> under...
>> But this still occurs.
>>
>> "Nikolai Sonin" wrote:
>> > I too had this problem. The way that I fixed it was by making sure
>> > that I
>> > could connect to the SQL server in question from the server that hosted
>> > the
>> > reporting services website. It was the that I realized two very
>> > important
>> > things.
>> > 1. The user profile that reporting services uses to run as a service
>> > needs
>> > SQL server access.
>> > 2. The user profile that I entered as the login for the report needed
>> > to be
>> > qualified with a domain name if it was a windows account.
>> > These may not be your problem, but they were mine. I hope this helps
>> >
>> >
>> > "PV" wrote:
>> >
>> > > Hi,
>> > > I am working with SQL Reporting services 2000... The report is
>> > > unable to
>> > > connect to a remote datasource as I try to preview it. The database
>> > > uses both
>> > > SQL and Windows Auth.
>> > >
>> > > 1. Created a shared datasource in my project
>> > > 2. Deployed it on the server. Used OverwriteDataSource = false
>> > > 3. Create a new shared datasource on the server. Gave a connection
>> > > string
>> > > with no username and password.
>> > > 4. Selected "Credentials stored securely in the report server" and
>> > > then
>> > > entered the SQL server login and passoword there.
>> > > 5. Changed the report properties in the manager to use this
>> > > datasource.
>> > > 6. While trying to "View" via report manager, I am getting the error
>> > > message
>> > >
>> > > An error has occurred during report processing. (rsProcessingAborted)
>> > > Get
>> > > Online Help
>> > > Cannot create a connection to data source 'xxxx'.
>> > > (rsErrorOpeningConnection)
>> > > Get Online Help
>> > > SQL Server does not exist or access denied.
>> > >
>> > >
>> > > CAN SOMEONE PLEASE HELP ME!!!!!!|||Thanks! I finally figure it out.
It was a permissions problem, but was masked because I thought it was using
the shared datasource. Instead it was using a report specific datasource that
had the same name. The way I discovered this was by going and taking a look
at the XML code of the problem report.
"Brian Welcker [MSFT]" wrote:
> There is no credential caching between data sources. Often this is some sort
> of network name resolution issue.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Leef" <Leef@.discussions.microsoft.com> wrote in message
> news:D69B9ED5-00A6-4ECF-9501-C269709E90B8@.microsoft.com...
> > I'm having a very similar problem, but I have two reports using the
> > datascource and rendering no problem and a third that gets the
> > rsErrorOpeningConnection error. This report previews just fine in VS, but
> > when not when deployed to the server. Anyone know if RS is does some sort
> > of
> > credential caching?
> >
> > "PV" wrote:
> >
> >> I confirmed this... Found out that I could connect from the Reporting
> >> services machine to my SQL Server using the same SQL credentials...
> >>
> >> Also gave access to the DB to the user that the Report Manager runs
> >> under...
> >>
> >> But this still occurs.
> >>
> >>
> >>
> >> "Nikolai Sonin" wrote:
> >>
> >> > I too had this problem. The way that I fixed it was by making sure
> >> > that I
> >> > could connect to the SQL server in question from the server that hosted
> >> > the
> >> > reporting services website. It was the that I realized two very
> >> > important
> >> > things.
> >> > 1. The user profile that reporting services uses to run as a service
> >> > needs
> >> > SQL server access.
> >> > 2. The user profile that I entered as the login for the report needed
> >> > to be
> >> > qualified with a domain name if it was a windows account.
> >> > These may not be your problem, but they were mine. I hope this helps
> >> >
> >> >
> >> > "PV" wrote:
> >> >
> >> > > Hi,
> >> > > I am working with SQL Reporting services 2000... The report is
> >> > > unable to
> >> > > connect to a remote datasource as I try to preview it. The database
> >> > > uses both
> >> > > SQL and Windows Auth.
> >> > >
> >> > > 1. Created a shared datasource in my project
> >> > > 2. Deployed it on the server. Used OverwriteDataSource = false
> >> > > 3. Create a new shared datasource on the server. Gave a connection
> >> > > string
> >> > > with no username and password.
> >> > > 4. Selected "Credentials stored securely in the report server" and
> >> > > then
> >> > > entered the SQL server login and passoword there.
> >> > > 5. Changed the report properties in the manager to use this
> >> > > datasource.
> >> > > 6. While trying to "View" via report manager, I am getting the error
> >> > > message
> >> > >
> >> > > An error has occurred during report processing. (rsProcessingAborted)
> >> > > Get
> >> > > Online Help
> >> > > Cannot create a connection to data source 'xxxx'.
> >> > > (rsErrorOpeningConnection)
> >> > > Get Online Help
> >> > > SQL Server does not exist or access denied.
> >> > >
> >> > >
> >> > > CAN SOMEONE PLEASE HELP ME!!!!!!
>
>

Please Help! Pl/sql

Hi,

I have a working SQL script which consists of some report formatting and a select statment. My problem is that this script is going to be set as a daily automated task on a solaris system which will generate a report. However, I am having extreme difficulty with making the report file name as the previous date in the format YYYYMMDD. (For example, today's date is August 6, 2003 and the automated task runs and it should generate a report with file name 20030805.lst) How do I do this? I have minimal knowledge with PL/SQL. I really don't have a clue how to write a procedure that can store sysdate-1 (which is essentially the previous day's date) in a variable for me to use in the SPOOL statement. I need to get this done asap and I really need help. I have looked at online documents but I don't understand how the block structure works.

How can I generate this file with the specific file name with my existing SQL statement?

Thanks in advance!!Did you try:

col mydate new_value _today
break on report
select to_char(sysdate,'YYYYMMDD') mydate from dual;
spool &_today
...etc...

PS: spool command cannot be used in PL/SQL, only in SQL Plus.

:cool:|||Thank you! It works perfectly...I've never thought of doing it this way!!

Please help! Partitioned View - slow performance (but was working before)

Hello everyone,
I have a tough problem I've been working on all morning. We have a very
large table which we've divided up into smaller monthly tables and now
access using a partitioned view. Queries on the table have been working
quite well in the past. However, on July 1 we added a new table to the
view to hold July data and now the performance is quite bad.
Queries which access either data up to June 30th run quickly, as does
any query which accesses solely July data. However, when I attempt to
run a query which spans the last few days of June and the first few
days of July, the query time goes from about 8 seconds to over 3
minutes! I do not have this problem when spanning earlier months (ie:
May to June, or April to May).
I've checked the indexes on the new table, and they are identical to
ones on the June table. I've looked at the query plan, but I don't see
anything out of the ordinary. At this point, I am really stumped. Any
suggestions? Let me know what other data I can provide to help diagnose
this.
Thanks!!rich@.adgooroo.com wrote:
> Hello everyone,
> I have a tough problem I've been working on all morning. We have a very
> large table which we've divided up into smaller monthly tables and now
> access using a partitioned view. Queries on the table have been working
> quite well in the past. However, on July 1 we added a new table to the
> view to hold July data and now the performance is quite bad.
> Queries which access either data up to June 30th run quickly, as does
> any query which accesses solely July data. However, when I attempt to
> run a query which spans the last few days of June and the first few
> days of July, the query time goes from about 8 seconds to over 3
> minutes! I do not have this problem when spanning earlier months (ie:
> May to June, or April to May).
> I've checked the indexes on the new table, and they are identical to
> ones on the June table. I've looked at the query plan, but I don't see
> anything out of the ordinary. At this point, I am really stumped. Any
> suggestions? Let me know what other data I can provide to help diagnose
> this.
> Thanks!!
>
Run a query from May 24 - June 7, and one from June 23 - July 2. Run
these together in the same Query Analyzer session, compare the execution
plans of the two. What is different?|||Have you got check constraints on the date column that is being used as the
partitioning column? Are all such check constraints mutually exclusive?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<rich@.adgooroo.com> wrote in message
news:1151867705.674924.96330@.p79g2000cwp.googlegroups.com...
Hello everyone,
I have a tough problem I've been working on all morning. We have a very
large table which we've divided up into smaller monthly tables and now
access using a partitioned view. Queries on the table have been working
quite well in the past. However, on July 1 we added a new table to the
view to hold July data and now the performance is quite bad.
Queries which access either data up to June 30th run quickly, as does
any query which accesses solely July data. However, when I attempt to
run a query which spans the last few days of June and the first few
days of July, the query time goes from about 8 seconds to over 3
minutes! I do not have this problem when spanning earlier months (ie:
May to June, or April to May).
I've checked the indexes on the new table, and they are identical to
ones on the June table. I've looked at the query plan, but I don't see
anything out of the ordinary. At this point, I am really stumped. Any
suggestions? Let me know what other data I can provide to help diagnose
this.
Thanks!!|||Hi,
The check constraints are properly set up on all tables:
June: ([observationtime] >= '6/1/2006' and [observationtime] <='6/30/2006')
July: ([observationtime] >= '7/1/2006' and [observationtime] <='7/31/2006')
I did discover one difference between the tables. The June table had
its indexes on our high-speed write drive. I moved it over to the
high-speed read drive (because the table is no longer being updated),
just as the April and May tables are. However, this did not improve
things any.
When running the two queries together in the same QA session, there are
some differences.
1. The query has a nested SELECT query within it. When running the
May-June query, the July table has a FILTER step applied to it (in the
Q/A execution plan). I assume that this is indicative of the date
constraint being applied. What I don't understand is why this FILTER
step isn't being applied to the April table.
2. When running the June-July query, the July table has an INDEX SEEK
step applied to it. This causes 46% of the execution time. The other
tables have a CLUSTERED INDEX SEEK applied to them, and they are each
accounting for only 2% of the query time. Why would a different index
be used on July?
I've uploaded images of the query plan for you to look at. The June
query is the fast one, while the July query is the slow one.
June query: http://www.goorooreviews.com/June%20query.gif
July query: http://www.goorooreviews.com/July%20query.gif
Thanks again!|||Is the check constraint on the July table trusted? IOW, what do you get
from this:
select
objectproperty (object_id ('the name of the check constraint'),
'CnstIsNotTrusted')
If it is 1, then recreate the check constraint WITH CHECK.
One note - though this should not change anything - I see that your check
constraints are on a column called observationtime. If you are using the
time component of a datetime column, keep in mind that on June 30, for
example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
23:59:59.997.
Could you please extract and post the DDL for each of the June and July
tables - including indexes and constraints?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
Hi,
The check constraints are properly set up on all tables:
June: ([observationtime] >= '6/1/2006' and [observationtime] <='6/30/2006')
July: ([observationtime] >= '7/1/2006' and [observationtime] <='7/31/2006')
I did discover one difference between the tables. The June table had
its indexes on our high-speed write drive. I moved it over to the
high-speed read drive (because the table is no longer being updated),
just as the April and May tables are. However, this did not improve
things any.
When running the two queries together in the same QA session, there are
some differences.
1. The query has a nested SELECT query within it. When running the
May-June query, the July table has a FILTER step applied to it (in the
Q/A execution plan). I assume that this is indicative of the date
constraint being applied. What I don't understand is why this FILTER
step isn't being applied to the April table.
2. When running the June-July query, the July table has an INDEX SEEK
step applied to it. This causes 46% of the execution time. The other
tables have a CLUSTERED INDEX SEEK applied to them, and they are each
accounting for only 2% of the query time. Why would a different index
be used on July?
I've uploaded images of the query plan for you to look at. The June
query is the fast one, while the July query is the slow one.
June query: http://www.goorooreviews.com/June%20query.gif
July query: http://www.goorooreviews.com/July%20query.gif
Thanks again!|||Ok, I figured out a part of the problem. The table itself is on our
high-speed read drive. This means the clustered index is also on the
same drive. However, the index that the query needs was on the
high-speed write drive. Because of this, it wasn't incorporating the
clustered index.
By putting the index on the same drive, the query time has come down to
about 60 seconds. Still too long, but some improvement.
Looking at the query analyzer results, I'm seeing some BOOKMARK LOOKUP
steps. I'm now attempting to rebuild the indexes to make them cover the
query. I don't know how well this will work, because the column that is
missing is an NVARCHAR(255) column, and it is going to add
significantly to the width of the index. But we'll see.|||Having them on the same drive or not doesn't affect whether or not an index
will be used. It depends on the data, the index keys and the index
statistics - given that the query remains the same.
Make sure the indexes are identical across all tables and that the index
stats are current. Be sure that the CHECK constraints have been created
WITH CHECK - not NOCHECK.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151873610.900389.187190@.75g2000cwc.googlegroups.com...
Ok, I figured out a part of the problem. The table itself is on our
high-speed read drive. This means the clustered index is also on the
same drive. However, the index that the query needs was on the
high-speed write drive. Because of this, it wasn't incorporating the
clustered index.
By putting the index on the same drive, the query time has come down to
about 60 seconds. Still too long, but some improvement.
Looking at the query analyzer results, I'm seeing some BOOKMARK LOOKUP
steps. I'm now attempting to rebuild the indexes to make them cover the
query. I don't know how well this will work, because the column that is
missing is an NVARCHAR(255) column, and it is going to add
significantly to the width of the index. But we'll see.|||Hi Tom,
It looks like this is the problem! April-June are all returning 1 as a
result of your query. I updated each constraint with the following SQL:
ALTER TABLE [dbo].[NaturalRank_2006_06] WITH CHECK CHECK constraint
CK_NaturalRank_2006_06
Now all of the tables return 0 (trusted).
The query performs MUCH better as a result. It's now executing in 14
seconds, and now all of the tables have a FILTER step being applied to
them (which is what I would expect).
There are still some more optimizations to be made, but these appear to
be pretty straightforward indexing problems.
Thanks for helping out, this was truly appreciated!!
-Rich
Tom Moreau wrote:
> Is the check constraint on the July table trusted? IOW, what do you get
> from this:
> select
> objectproperty (object_id ('the name of the check constraint'),
> 'CnstIsNotTrusted')
> If it is 1, then recreate the check constraint WITH CHECK.
> One note - though this should not change anything - I see that your check
> constraints are on a column called observationtime. If you are using the
> time component of a datetime column, keep in mind that on June 30, for
> example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
> 23:59:59.997.
> Could you please extract and post the DDL for each of the June and July
> tables - including indexes and constraints?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
> Hi,
> The check constraints are properly set up on all tables:
> June: ([observationtime] >= '6/1/2006' and [observationtime] <=> '6/30/2006')
> July: ([observationtime] >= '7/1/2006' and [observationtime] <=> '7/31/2006')
> I did discover one difference between the tables. The June table had
> its indexes on our high-speed write drive. I moved it over to the
> high-speed read drive (because the table is no longer being updated),
> just as the April and May tables are. However, this did not improve
> things any.
> When running the two queries together in the same QA session, there are
> some differences.
> 1. The query has a nested SELECT query within it. When running the
> May-June query, the July table has a FILTER step applied to it (in the
> Q/A execution plan). I assume that this is indicative of the date
> constraint being applied. What I don't understand is why this FILTER
> step isn't being applied to the April table.
> 2. When running the June-July query, the July table has an INDEX SEEK
> step applied to it. This causes 46% of the execution time. The other
> tables have a CLUSTERED INDEX SEEK applied to them, and they are each
> accounting for only 2% of the query time. Why would a different index
> be used on July?
> I've uploaded images of the query plan for you to look at. The June
> query is the fast one, while the July query is the slow one.
> June query: http://www.goorooreviews.com/June%20query.gif
> July query: http://www.goorooreviews.com/July%20query.gif
> Thanks again!|||My pleasure. As it turns out, I'm working on partitioned views for a
client, so I know where all the bones are buried. :-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151874763.783714.81740@.j8g2000cwa.googlegroups.com...
Hi Tom,
It looks like this is the problem! April-June are all returning 1 as a
result of your query. I updated each constraint with the following SQL:
ALTER TABLE [dbo].[NaturalRank_2006_06] WITH CHECK CHECK constraint
CK_NaturalRank_2006_06
Now all of the tables return 0 (trusted).
The query performs MUCH better as a result. It's now executing in 14
seconds, and now all of the tables have a FILTER step being applied to
them (which is what I would expect).
There are still some more optimizations to be made, but these appear to
be pretty straightforward indexing problems.
Thanks for helping out, this was truly appreciated!!
-Rich
Tom Moreau wrote:
> Is the check constraint on the July table trusted? IOW, what do you get
> from this:
> select
> objectproperty (object_id ('the name of the check constraint'),
> 'CnstIsNotTrusted')
> If it is 1, then recreate the check constraint WITH CHECK.
> One note - though this should not change anything - I see that your check
> constraints are on a column called observationtime. If you are using the
> time component of a datetime column, keep in mind that on June 30, for
> example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
> 23:59:59.997.
> Could you please extract and post the DDL for each of the June and July
> tables - including indexes and constraints?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
> Hi,
> The check constraints are properly set up on all tables:
> June: ([observationtime] >= '6/1/2006' and [observationtime] <=> '6/30/2006')
> July: ([observationtime] >= '7/1/2006' and [observationtime] <=> '7/31/2006')
> I did discover one difference between the tables. The June table had
> its indexes on our high-speed write drive. I moved it over to the
> high-speed read drive (because the table is no longer being updated),
> just as the April and May tables are. However, this did not improve
> things any.
> When running the two queries together in the same QA session, there are
> some differences.
> 1. The query has a nested SELECT query within it. When running the
> May-June query, the July table has a FILTER step applied to it (in the
> Q/A execution plan). I assume that this is indicative of the date
> constraint being applied. What I don't understand is why this FILTER
> step isn't being applied to the April table.
> 2. When running the June-July query, the July table has an INDEX SEEK
> step applied to it. This causes 46% of the execution time. The other
> tables have a CLUSTERED INDEX SEEK applied to them, and they are each
> accounting for only 2% of the query time. Why would a different index
> be used on July?
> I've uploaded images of the query plan for you to look at. The June
> query is the fast one, while the July query is the slow one.
> June query: http://www.goorooreviews.com/June%20query.gif
> July query: http://www.goorooreviews.com/July%20query.gif
> Thanks again!

Please help! Partitioned View - slow performance (but was working before)

Hello everyone,
I have a tough problem I've been working on all morning. We have a very
large table which we've divided up into smaller monthly tables and now
access using a partitioned view. Queries on the table have been working
quite well in the past. However, on July 1 we added a new table to the
view to hold July data and now the performance is quite bad.
Queries which access either data up to June 30th run quickly, as does
any query which accesses solely July data. However, when I attempt to
run a query which spans the last few days of June and the first few
days of July, the query time goes from about 8 seconds to over 3
minutes! I do not have this problem when spanning earlier months (ie:
May to June, or April to May).
I've checked the indexes on the new table, and they are identical to
ones on the June table. I've looked at the query plan, but I don't see
anything out of the ordinary. At this point, I am really stumped. Any
suggestions? Let me know what other data I can provide to help diagnose
this.
Thanks!!Have you got check constraints on the date column that is being used as the
partitioning column? Are all such check constraints mutually exclusive?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<rich@.adgooroo.com> wrote in message
news:1151867705.674924.96330@.p79g2000cwp.googlegroups.com...
Hello everyone,
I have a tough problem I've been working on all morning. We have a very
large table which we've divided up into smaller monthly tables and now
access using a partitioned view. Queries on the table have been working
quite well in the past. However, on July 1 we added a new table to the
view to hold July data and now the performance is quite bad.
Queries which access either data up to June 30th run quickly, as does
any query which accesses solely July data. However, when I attempt to
run a query which spans the last few days of June and the first few
days of July, the query time goes from about 8 seconds to over 3
minutes! I do not have this problem when spanning earlier months (ie:
May to June, or April to May).
I've checked the indexes on the new table, and they are identical to
ones on the June table. I've looked at the query plan, but I don't see
anything out of the ordinary. At this point, I am really stumped. Any
suggestions? Let me know what other data I can provide to help diagnose
this.
Thanks!!|||Hi,
The check constraints are properly set up on all tables:
June: ([observationtime] >= '6/1/2006' and [observationtime] <=
'6/30/2006')
July: ([observationtime] >= '7/1/2006' and [observationtime] <=
'7/31/2006')
I did discover one difference between the tables. The June table had
its indexes on our high-speed write drive. I moved it over to the
high-speed read drive (because the table is no longer being updated),
just as the April and May tables are. However, this did not improve
things any.
When running the two queries together in the same QA session, there are
some differences.
1. The query has a nested SELECT query within it. When running the
May-June query, the July table has a FILTER step applied to it (in the
Q/A execution plan). I assume that this is indicative of the date
constraint being applied. What I don't understand is why this FILTER
step isn't being applied to the April table.
2. When running the June-July query, the July table has an INDEX SEEK
step applied to it. This causes 46% of the execution time. The other
tables have a CLUSTERED INDEX SEEK applied to them, and they are each
accounting for only 2% of the query time. Why would a different index
be used on July?
I've uploaded images of the query plan for you to look at. The June
query is the fast one, while the July query is the slow one.
June query: http://www.goorooreviews.com/June%20query.gif
July query: http://www.goorooreviews.com/July%20query.gif
Thanks again!|||Is the check constraint on the July table trusted? IOW, what do you get
from this:
select
objectproperty (object_id ('the name of the check constraint'),
'CnstIsNotTrusted')
If it is 1, then recreate the check constraint WITH CHECK.
One note - though this should not change anything - I see that your check
constraints are on a column called observationtime. If you are using the
time component of a datetime column, keep in mind that on June 30, for
example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
23:59:59.997.
Could you please extract and post the DDL for each of the June and July
tables - including indexes and constraints?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
Hi,
The check constraints are properly set up on all tables:
June: ([observationtime] >= '6/1/2006' and [observationtime] <=
'6/30/2006')
July: ([observationtime] >= '7/1/2006' and [observationtime] <=
'7/31/2006')
I did discover one difference between the tables. The June table had
its indexes on our high-speed write drive. I moved it over to the
high-speed read drive (because the table is no longer being updated),
just as the April and May tables are. However, this did not improve
things any.
When running the two queries together in the same QA session, there are
some differences.
1. The query has a nested SELECT query within it. When running the
May-June query, the July table has a FILTER step applied to it (in the
Q/A execution plan). I assume that this is indicative of the date
constraint being applied. What I don't understand is why this FILTER
step isn't being applied to the April table.
2. When running the June-July query, the July table has an INDEX SEEK
step applied to it. This causes 46% of the execution time. The other
tables have a CLUSTERED INDEX SEEK applied to them, and they are each
accounting for only 2% of the query time. Why would a different index
be used on July?
I've uploaded images of the query plan for you to look at. The June
query is the fast one, while the July query is the slow one.
June query: http://www.goorooreviews.com/June%20query.gif
July query: http://www.goorooreviews.com/July%20query.gif
Thanks again!|||Ok, I figured out a part of the problem. The table itself is on our
high-speed read drive. This means the clustered index is also on the
same drive. However, the index that the query needs was on the
high-speed write drive. Because of this, it wasn't incorporating the
clustered index.
By putting the index on the same drive, the query time has come down to
about 60 seconds. Still too long, but some improvement.
Looking at the query analyzer results, I'm seeing some BOOKMARK LOOKUP
steps. I'm now attempting to rebuild the indexes to make them cover the
query. I don't know how well this will work, because the column that is
missing is an NVARCHAR(255) column, and it is going to add
significantly to the width of the index. But we'll see.|||Having them on the same drive or not doesn't affect whether or not an index
will be used. It depends on the data, the index keys and the index
statistics - given that the query remains the same.
Make sure the indexes are identical across all tables and that the index
stats are current. Be sure that the CHECK constraints have been created
WITH CHECK - not NOCHECK.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151873610.900389.187190@.75g2000cwc.googlegroups.com...
Ok, I figured out a part of the problem. The table itself is on our
high-speed read drive. This means the clustered index is also on the
same drive. However, the index that the query needs was on the
high-speed write drive. Because of this, it wasn't incorporating the
clustered index.
By putting the index on the same drive, the query time has come down to
about 60 seconds. Still too long, but some improvement.
Looking at the query analyzer results, I'm seeing some BOOKMARK LOOKUP
steps. I'm now attempting to rebuild the indexes to make them cover the
query. I don't know how well this will work, because the column that is
missing is an NVARCHAR(255) column, and it is going to add
significantly to the width of the index. But we'll see.|||Hi Tom,
It looks like this is the problem! April-June are all returning 1 as a
result of your query. I updated each constraint with the following SQL:
ALTER TABLE [dbo].[NaturalRank_2006_06] WITH CHECK CHECK constraint
CK_NaturalRank_2006_06
Now all of the tables return 0 (trusted).
The query performs MUCH better as a result. It's now executing in 14
seconds, and now all of the tables have a FILTER step being applied to
them (which is what I would expect).
There are still some more optimizations to be made, but these appear to
be pretty straightforward indexing problems.
Thanks for helping out, this was truly appreciated!!
-Rich
Tom Moreau wrote:
> Is the check constraint on the July table trusted? IOW, what do you get
> from this:
> select
> objectproperty (object_id ('the name of the check constraint'),
> 'CnstIsNotTrusted')
> If it is 1, then recreate the check constraint WITH CHECK.
> One note - though this should not change anything - I see that your check
> constraints are on a column called observationtime. If you are using the
> time component of a datetime column, keep in mind that on June 30, for
> example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
> 23:59:59.997.
> Could you please extract and post the DDL for each of the June and July
> tables - including indexes and constraints?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
> Hi,
> The check constraints are properly set up on all tables:
> June: ([observationtime] >= '6/1/2006' and [observationtime] <=
> '6/30/2006')
> July: ([observationtime] >= '7/1/2006' and [observationtime] <=
> '7/31/2006')
> I did discover one difference between the tables. The June table had
> its indexes on our high-speed write drive. I moved it over to the
> high-speed read drive (because the table is no longer being updated),
> just as the April and May tables are. However, this did not improve
> things any.
> When running the two queries together in the same QA session, there are
> some differences.
> 1. The query has a nested SELECT query within it. When running the
> May-June query, the July table has a FILTER step applied to it (in the
> Q/A execution plan). I assume that this is indicative of the date
> constraint being applied. What I don't understand is why this FILTER
> step isn't being applied to the April table.
> 2. When running the June-July query, the July table has an INDEX SEEK
> step applied to it. This causes 46% of the execution time. The other
> tables have a CLUSTERED INDEX SEEK applied to them, and they are each
> accounting for only 2% of the query time. Why would a different index
> be used on July?
> I've uploaded images of the query plan for you to look at. The June
> query is the fast one, while the July query is the slow one.
> June query: http://www.goorooreviews.com/June%20query.gif
> July query: http://www.goorooreviews.com/July%20query.gif
> Thanks again!|||My pleasure. As it turns out, I'm working on partitioned views for a
client, so I know where all the bones are buried. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151874763.783714.81740@.j8g2000cwa.googlegroups.com...
Hi Tom,
It looks like this is the problem! April-June are all returning 1 as a
result of your query. I updated each constraint with the following SQL:
ALTER TABLE [dbo].[NaturalRank_2006_06] WITH CHECK CHECK constraint
CK_NaturalRank_2006_06
Now all of the tables return 0 (trusted).
The query performs MUCH better as a result. It's now executing in 14
seconds, and now all of the tables have a FILTER step being applied to
them (which is what I would expect).
There are still some more optimizations to be made, but these appear to
be pretty straightforward indexing problems.
Thanks for helping out, this was truly appreciated!!
-Rich
Tom Moreau wrote:
> Is the check constraint on the July table trusted? IOW, what do you get
> from this:
> select
> objectproperty (object_id ('the name of the check constraint'),
> 'CnstIsNotTrusted')
> If it is 1, then recreate the check constraint WITH CHECK.
> One note - though this should not change anything - I see that your check
> constraints are on a column called observationtime. If you are using the
> time component of a datetime column, keep in mind that on June 30, for
> example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
> 23:59:59.997.
> Could you please extract and post the DDL for each of the June and July
> tables - including indexes and constraints?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
> Hi,
> The check constraints are properly set up on all tables:
> June: ([observationtime] >= '6/1/2006' and [observationtime] <=
> '6/30/2006')
> July: ([observationtime] >= '7/1/2006' and [observationtime] <=
> '7/31/2006')
> I did discover one difference between the tables. The June table had
> its indexes on our high-speed write drive. I moved it over to the
> high-speed read drive (because the table is no longer being updated),
> just as the April and May tables are. However, this did not improve
> things any.
> When running the two queries together in the same QA session, there are
> some differences.
> 1. The query has a nested SELECT query within it. When running the
> May-June query, the July table has a FILTER step applied to it (in the
> Q/A execution plan). I assume that this is indicative of the date
> constraint being applied. What I don't understand is why this FILTER
> step isn't being applied to the April table.
> 2. When running the June-July query, the July table has an INDEX SEEK
> step applied to it. This causes 46% of the execution time. The other
> tables have a CLUSTERED INDEX SEEK applied to them, and they are each
> accounting for only 2% of the query time. Why would a different index
> be used on July?
> I've uploaded images of the query plan for you to look at. The June
> query is the fast one, while the July query is the slow one.
> June query: http://www.goorooreviews.com/June%20query.gif
> July query: http://www.goorooreviews.com/July%20query.gif
> Thanks again!