Differing results on identical systems.

I have a staging environment and a developement environment set up on the same machine.
Both environments consist of two databases, a staging area and a data warehouse.

On the staging system when I run a query of the form:
select
count(*)
from [StagingArea].[dbo].[saTable]
join [DataWarehouse].[dbo].[dwView] on saTable.col = dwView.col
join [DataWarehouse].[dbo].[dwTable1] on dwTable1.col = dwView.col
join [DataWarehouse].[dbo].[dwTable2] on (dwTable1.col = dwTable2.col AND dwTable2.col = saTable.col)
I get an answer returned in 2 seconds.

When I run the same query on the developement system it never comes back with an answer (after 3+ hours).

The data in both systems is identical. But it would appear that the query optimizer is behaving differently on the two identical systems.

I am looking for a way to enforce the same behavior in both systems. Any tips would be appreciated.

Thanks.
-PMP

have u checked statistics on development , there may be lot of insert/update/delete action going on Develpment server for testing purpose. So reindex the tables invoved and run sp_updatestats.

Madhu

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegroups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

different results in query analyzer and .net

ok the following statement returns the correct results in sql query analyzer but in the .net environment with c# it returns back less 3 records

SELECT SUM(ao.amount),bl.dpc,bl.city from billinglocation bl
inner join shippinglocation sl on bl.billinglocationid = sl.billinglocationid
inner join absorbentorder ao on sl.shippinglocationid = ao.shippinglocationid
group by bl.dpc, bl.city

billinglocation 1 - 1 shippinglocation 1 - many absorbentorder

anybody have any ideas why this might be occuring
thanks!

are you connecting to the right database?|||If you are receiving different results then you are not running the exact same thing. Are there any parameters involved? As Dinakar asked, are you sure you are running the query against the same database? We are probably going to need to see your C# code in order to be of any real help.

Different execution plans - same data, same server

Hi there - hoping someone can help me here!

I have a database that has been underperforming on a number of queries
recently - in a test environment they take only a few seconds, but on
the live data they take up to a minute or so to run. This is using the
same data.

Every evening a copy of the live data is copied to a backup 'snapshot'
database on the same server and also, on this copy the queries only
take a second or so to run. (This is testing through the Query
Analyser)

I've studied the execution plans for the same query on the snapshot db
and the live db and they seem to be significantly different - why is
this? it's looking at the same data and exactly the same code!!

Anybody got any ideas?Was the 'snapshot' created with BACKUP/RESTORE or some other ETL technique?
If not BACKUP/RESTORE, it may be that statistics are different, resulting in
different plans. You might try updating stats on your live database

--
Hope this helps.

Dan Guzman
SQL Server MVP

"James Walker" <james@.jimw.co.uk> wrote in message
news:8228bccf.0412080431.793b31e3@.posting.google.c om...
> Hi there - hoping someone can help me here!
> I have a database that has been underperforming on a number of queries
> recently - in a test environment they take only a few seconds, but on
> the live data they take up to a minute or so to run. This is using the
> same data.
> Every evening a copy of the live data is copied to a backup 'snapshot'
> database on the same server and also, on this copy the queries only
> take a second or so to run. (This is testing through the Query
> Analyser)
> I've studied the execution plans for the same query on the snapshot db
> and the live db and they seem to be significantly different - why is
> this? it's looking at the same data and exactly the same code!!
> Anybody got any ideas?|||"Copy of the Live Data" - as in backup/restore? I'd update statistics
on both - then check the execution plan. I've seen the "wrong" plan
taken becuase statistics were up to date - opposite of what you would
expect.|||Thanks for all your help people - i did:

UPDATE STATISTICS tablename

for all the dependent tables involved and its now fixed it and is
working nice and fast again...

one question still however, is why i've needed to do this?? Shouldn't
it automatically keep things like this up to date?

different environment fails to fill a dataset

I have a C# client application that uses SQL Server as a backend. It works
fine on my development workstation. But when I moved it to another test
workstation four out of the five winforms worked fine but one doesn't. The
one that doesn't can open the connection and return the server version as a
test of whether the open worked right. I does. But when you run the
oledbadapter.fill command to fill the dataset it fails. It acts as if it
can't open the server. Further reading shows that the fill command can
actually open a connection on its own if necessary without the open command.
Is there anything in the environment or SQL Server that could be causing this
rejection. The SQL Server database was detached, copied, and then reattached
on the test workstation. SO it should be a duplicate.
Randy
Did you resync the logins to the users in that db after you attached it? If
not check out sp_change_users_login in BooksOnLine.
Andrew J. Kelly SQL MVP
"rseedle" <rseedle@.discussions.microsoft.com> wrote in message
news:C298B6AF-C57D-40EE-905C-CB50F29126F3@.microsoft.com...
>I have a C# client application that uses SQL Server as a backend. It works
> fine on my development workstation. But when I moved it to another test
> workstation four out of the five winforms worked fine but one doesn't. The
> one that doesn't can open the connection and return the server version as
> a
> test of whether the open worked right. I does. But when you run the
> oledbadapter.fill command to fill the dataset it fails. It acts as if it
> can't open the server. Further reading shows that the fill command can
> actually open a connection on its own if necessary without the open
> command.
> Is there anything in the environment or SQL Server that could be causing
> this
> rejection. The SQL Server database was detached, copied, and then
> reattached
> on the test workstation. SO it should be a duplicate.
> Randy

Different Default Date format for 2 SQL Server Instances

Hi,
We recently had a new environment created. The servers were all installed
as separate instances on the same physical machine.
For my first instance INST1
When I execute the following query exec getMyData '1964-11-19'
in query analyser everything is fine
from my (ASP) website everything is fine
For my second instance INST2
in query analyser everything is fine
from my asp website I get varchar cannot be converted to datetime.
I can only believe that the default settings for the server were different
when each of the SQL Server Installs were performed.
I cannot change the way we pass dates in our website because it is a massive
re-write of everytihg if I do.
Is there some why of changing the default settings of the server after
installing?
I have tried using
sp_configure
SET language
sp_defaultlanguage
and all these methods did not fix my problem.
Any ideas?
JayneK wrote:
> Hi,
> We recently had a new environment created. The servers were all
> installed as separate instances on the same physical machine.
> For my first instance INST1
> When I execute the following query exec getMyData '1964-11-19'
> in query analyser everything is fine
> from my (ASP) website everything is fine
> For my second instance INST2
> in query analyser everything is fine
> from my asp website I get varchar cannot be converted to datetime.
> I can only believe that the default settings for the server were
> different when each of the SQL Server Installs were performed.
> I cannot change the way we pass dates in our website because it is a
> massive re-write of everytihg if I do.
> Is there some why of changing the default settings of the server after
> installing?
> I have tried using
> sp_configure
> SET language
> sp_defaultlanguage
> and all these methods did not fix my problem.
> Any ideas?
When working with dates in character format, you should only ever use a
portable format. Two formats are supported that will never cause
problems related to the server's regional settings:
yyyy-mm-ddThh:mm:ss.mmm (no spaces)
yyyymmdd
What is probably occurring is that one server is using MDY format and
the other is using DMY.
For example:
SET NOCOUNT ON
SET DATEFORMAT MDY
SELECT CAST('1964-11-19' as DATETIME)
SET DATEFORMAT DMY
SELECT CAST('1964-11-19' as DATETIME)
-- Results
1964-11-19 00:00:00.000
Server: Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
David Gugick - SQL Server MVP
Quest Software
|||David,
Unfortunately they all have all their language setting exactly the same. So
they are all set to us_english as default language, yet one server acts
differently to the other.
How can we fix this? do we have to uninstall and re-install, can't we hack
a file or something?
"David Gugick" wrote:

> JayneK wrote:
> When working with dates in character format, you should only ever use a
> portable format. Two formats are supported that will never cause
> problems related to the server's regional settings:
> yyyy-mm-ddThh:mm:ss.mmm (no spaces)
> yyyymmdd
>
> What is probably occurring is that one server is using MDY format and
> the other is using DMY.
> For example:
> SET NOCOUNT ON
> SET DATEFORMAT MDY
> SELECT CAST('1964-11-19' as DATETIME)
> SET DATEFORMAT DMY
> SELECT CAST('1964-11-19' as DATETIME)
> -- Results
> 1964-11-19 00:00:00.000
> Server: Msg 242, Level 16, State 3, Line 9
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||JayneK wrote:[vbcol=seagreen]
> David,
> Unfortunately they all have all their language setting exactly the
> same. So they are all set to us_english as default language, yet one
> server acts differently to the other.
> How can we fix this? do we have to uninstall and re-install, can't
> we hack a file or something?
> "David Gugick" wrote:
The ASP web site client is likely set up different. Go to that PC, open
up QA, and run the example above. The problem is that you are not using
a portable date format and are bound to run into these types of
problems. If you get rid of the hyphens in the date parameter, that will
probably fix the issue. Since you cannot easily change the code
executing the date procedure you wrote, why not just change the
procedure itself to strip the hyphens out using Set @.MyDate =
REPLACE(@.MyDate, '-', '')
David Gugick - SQL Server MVP
Quest Software