Footer rows to skip: box

Showing posts with label box. Show all posts

Friday, March 23, 2012

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restor
ed
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok an
d
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I go
t
> at the end told me that DB1 had been restored...when in fact it was DB2 th
at
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
Andre
Andre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Friday, March 9, 2012

Fastest connection method?

I am trying to find some info on the fastest connection transport for an app that is running on the same box as a SQL 2005 instance. The app does a large number of updates (high volume of data).. win32 using native ODBC. I am trying to find info on which connection mechanism is best... socket, pipes, etc. I read somewhere that there is a file mapped method available but i cannot find info on that either. Also, is there any performance difference between the old SQL OCBD drive and the new SQL Native Client OCBD drive?

Thanks.

For best performance, I recommend you use the SQL Native Client ODBC driver with SQL 2005. I would also recommend you use TCP/IP as the transport.

Regards,

Uwa.

|||Thanks for the info... does anyone know of any benchmarks for these methods? I have seen it written that shared memory should be used for best performance when on the same box as the SQL server... but have never seen numbers to prove or disprove this.

Wednesday, March 7, 2012

faster box but slower database

Hi,
Got two SQL servers (production,dev) and I noticed recently that my
production servers is much slower when running one import stored
procedure. The stored proc only uses tables variables a lot and only
performs selects on actual tables. The specs are:
Productions server:
SQL 2000 v. 8002039 (SP4)
2x Dual Core Intel Xeon 3,2GH
1GB Memory
RAID 1
Windows 2003 Server
Development server:
SQL 2000 v. 800194 (no service pack)
1x Dual Core Intel Pentium 4 3GHz
Windows XP Professional
The stored procedures takes 20 sec running on the dev box but 2.4 min
on the production box.
All indexes and tables are the same in both databases. I tried
updating the stats, rebuild indexes, recompile the stored proc, free
the proc cache but without any luck.
Any help would be very much appricated.
KristjanOne thing I forgot to mention. The execution tree found in the
profiler differ for the same query in diffrent databases.|||You didn't mention how much ram is on the dev box.
Also how many rows and what is the DB size? 1GB of ram on a production SQL
box with that kind of CPU seems way low to me.
Mike
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138125661.913476.224860@.g14g2000cwa.googlegroups.com...
> Hi,
> Got two SQL servers (production,dev) and I noticed recently that my
> production servers is much slower when running one import stored
> procedure. The stored proc only uses tables variables a lot and only
> performs selects on actual tables. The specs are:
> Productions server:
> SQL 2000 v. 8002039 (SP4)
> 2x Dual Core Intel Xeon 3,2GH
> 1GB Memory
> RAID 1
> Windows 2003 Server
> Development server:
> SQL 2000 v. 800194 (no service pack)
> 1x Dual Core Intel Pentium 4 3GHz
> Windows XP Professional
> The stored procedures takes 20 sec running on the dev box but 2.4 min
> on the production box.
> All indexes and tables are the same in both databases. I tried
> updating the stats, rebuild indexes, recompile the stored proc, free
> the proc cache but without any luck.
> Any help would be very much appricated.
> Kristjan
>|||The dev box also got 1 GB ram. The databases are almost the same size
because the app is not writing very much on runtime.
The stored proc spends most time on single table containing about
600,000 rows (both dev and production server). By the way, the
profiler tells me the production servers is doing ~10 times more reads
and spending ~10 more CPU than the dev server.
The total databsize is about 0,5 GB but maybe 1/4 of it is accessed
realtime.|||What different you found in Execution plan?
Try to run query on Prod. using option (maxdop 1).
If you have joins check that fields on both sides of the join have same
datatypes.
Regards
Amish Shah|||Tried the maxdop 1 option, no big difference.
The datatypes were ok.
Installed the database on my laptop to reproduce this. With no service
pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
Seems like updated server optimizer is not doing a good job with my
stored proc. Collecting some more data which I will post to this
thread soon.|||There have been a number of changes from GOLD and SP1 to SP4. A number of
these fixes result in different query plans being generated. It is not a far
comparison of performance between GOLD and SP4. Your GOLD version is also a
lot less secure in that it does not protect against SLAMMER.
Chris
"kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
> Tried the maxdop 1 option, no big difference.
> The datatypes were ok.
> Installed the database on my laptop to reproduce this. With no service
> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
> Seems like updated server optimizer is not doing a good job with my
> stored proc. Collecting some more data which I will post to this
> thread soon.
>|||This may sound silly, but have you opened the sproc on the SP4 box and
recompiled it on SP4. I have seen a few odd things from time to time when
sprocs were compiled on a lower SPack DB then restored to a differnt SPack
server. Views too.
At least you would give the SP4 query optimizer a chance to take a look at
the sproc to build a new plan to store with the sproc.
Mike
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uu3f3HpIGHA.532@.TK2MSFTNGP15.phx.gbl...
> There have been a number of changes from GOLD and SP1 to SP4. A number of
> these fixes result in different query plans being generated. It is not a
> far comparison of performance between GOLD and SP4. Your GOLD version is
> also a lot less secure in that it does not protect against SLAMMER.
> Chris
> "kgb" <kristjan.gudni.bjarnason@.gmail.com> wrote in message
> news:1138274581.095820.120850@.o13g2000cwo.googlegroups.com...
>> Tried the maxdop 1 option, no big difference.
>> The datatypes were ok.
>> Installed the database on my laptop to reproduce this. With no service
>> pack the stored proc ran fine (30 secs). With SP4 it ran for minutes.
>> Seems like updated server optimizer is not doing a good job with my
>> stored proc. Collecting some more data which I will post to this
>> thread soon.
>|||can you post the DDL and query you are running?|||Currently changing the query to "fit" the SP4 optimizer and think I
almost there, meaning the duration of the query is dropping to what I
expected. The changes sofar involved rewriting of several joins. I
get back to the thread in few hours or less - hopfully with result of
success. Thanks for all your replies. Its amazing to find the support
available on this group.|||Below you find the exec tree for the query, both versions. Being
novice mssql programmer I am not sure what part of the tree is taking
most time. The execution plan makes not much sense to me - the figures
are cost estimated rows seem all wrong.
the execution tree from the profilier is like this for the SP4 box.
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1009]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1009]=getidentity(1295343679, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Inner Join,
WHERE:([f1].[toairport]<>[a].[AirportCode]))
| | |--Nested Loops(Inner Join,
WHERE:([f2].[ToAirport]<>[b].[AirportCode]))
| | | |--Hash Match(Inner Join,
HASH:([f1].[toaptjoin])=([f2].[FromAptJoin]),
RESIDUAL:([f1].[fromairport]<>[f3].[fromairport] AND
((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0))))
| | | | |--Table Scan(OBJECT:(@.f1 AS [f1]),
WHERE:([f1].[outbound]=1))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1002]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([d].[date], [d].[weekday], [f3].[fromaptjoin]))
| | | | |--Nested Loops(Inner Join)
| | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Table
Scan(OBJECT:(@.weekdays_out AS [d]))
| | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin]),
WHERE:((([f2].[ValidFrom]<=[d].[date] AND [f2].[ValidTo]>=[d].[date])
AND [f2].[Status]=0) AND (Convert([f2].[Weekdays])&[d].[weekday])>0)
ORDERED FORWARD)
| | | |--Table Scan(OBJECT:(@.arrivalList AS [b]))
| | |--Table Scan(OBJECT:(@.arrivalList AS [a]))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)
and for the GOLD box its like:
Execution Tree
--
Table Insert(OBJECT:(@.f3), SET:(@.f3.[outbound]=RaiseIfNull(1),
@.f3.[groundDur_2]=[t2].[TransitTime],
@.f3.[groundDur_1]=[t1].[TransitTime], @.f3.[duration_3]=[f3].[Duration],
@.f3.[duration_2]=[f2].[Duration], @.f3.[duration_1]=[f1].[Duration],
@.f3.[FlightDate_3]=[f3].[FlightDate],
@.f3.[f1_FlightID]=[f2].[FlightID], @.f3.[flightset_3]=[f3].[flightset],
@.f3.[FlightID_3]=[f3].[FlightID],
@.f3.[fromairport_3]=[f3].[fromairport],
@.f3.[toairport_3]=[f3].[toairport], @.f3.[eta_3]=[f3].[eta],
@.f3.[std_3]=[f3].[std], @.f3.[FlightDate_2]=RaiseIfNull([@.out]),
@.f3.[f0_ID]=[f1].[ID], @.f3.[f0_FlightID]=[f1].[FlightID],
@.f3.[flightset_2]=[f2].[FlightSet], @.f3.[FlightID_2]=[f2].[FlightID],
@.f3.[fromairport_2]=[f2].[FromAirport],
@.f3.[toairport_2]=[f2].[ToAirport], @.f3.[eta_2]=[f2].[ETA],
@.f3.[std_2]=[f2].[STD], @.f3.[flightdate_1]=[f1].[FlightDate],
@.f3.[flightset_1]=[f1].[flightset], @.f3.[FlightID_1]=[f1].[FlightID],
@.f3.[fromairport_1]=[f1].[fromairport],
@.f3.[toairport_1]=[f1].[toairport], @.f3.[eta_1]=[f1].[eta],
@.f3.[std_1]=[f1].[std], @.f3.[ID]=RaiseIfNull([Expr1011]),
@.f3.[FlightNumber_1]=[f1].[FlightNumber],
@.f3.[AirlineCode_1]=[f1].[AirlineCode],
@.f3.[FlightNumber_2]=[f2].[FlightNumber],
@.f3.[AirlineCode_2]=[f2].[AirlineCode],
@.f3.[FlightNumber_3]=[f3].[FlightNumber],
@.f3.[AirlineCode_3]=[f3].[AirlineCode]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([Expr1011]=getidentity(1329804195, 2,
'@.f3')))
|--Filter(WHERE:([f3].[std]>[f2].[ETA]+Convert([@.transitDuration])+isnull([t2].[TransitTime],
0)))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([f2].[ArrTerm],
[f2].[ToAirport], [f3].[depTerm], [f3].[fromairport]))
|--Filter(WHERE:([f2].[STD]>[f1].[eta]+Convert([@.transitDuration])+isnull([t1].[TransitTime],
0)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f1].[arrTerm],
[f1].[toairport], [f2].[DepTerm], [f2].[FromAirport]))
| |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f2].[ToAirport]=@.arrivalList.[AirportCode]))
| | |--Nested Loops(Left Anti Semi Join,
WHERE:(@.arrivalList.[AirportCode]=NULL OR
[f1].[toairport]=@.arrivalList.[AirportCode]))
| | | |--Nested Loops(Inner Join,
WHERE:(((((([f1].[eta]-[f1].[std])*[f3].[dayafter]>0 AND
([f1].[eta]-[f1].[std])*[d].[dayafter]>0) OR ([f1].[eta]>[f1].[std] AND
([f2].[ETA]-[f2].[STD])*[f3].[dayafter]>0)) AND
Convert([f2].[Weekdays])&[d].[weekday]>0) AND
[f2].[ValidFrom]<=[d].[date]) AND [f2].[ValidTo]>=[d].[date]))
| | | | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([dohop].[dbo].[Flight1] AS [f2]))
| | | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([f1].[toaptjoin], [f3].[fromaptjoin]))
| | | | | |--Nested Loops(Inner Join,
WHERE:([f1].[fromairport]<>[f3].[fromairport]))
| | | | | | |--Table Scan(OBJECT:(@.f1 AS
[f1]), WHERE:([f1].[outbound]=1))
| | | | | | |--Table Scan(OBJECT:(@.flast AS
[f3]), WHERE:([f3].[outbound]=1))
| | | | | |--Index
Seek(OBJECT:([dohop].[dbo].[Flight1].[IX3_Flight1] AS [f2]),
SEEK:([f2].[ToAptJoin]=[f3].[fromaptjoin] AND
[f2].[FromAptJoin]=[f1].[toaptjoin] AND [f2].[Status]=0) ORDERED
FORWARD)
| | | | |--Table Scan(OBJECT:(@.weekdays_out AS [d]))
| | | |--Table Scan(OBJECT:(@.arrivalList))
| | |--Table Scan(OBJECT:(@.arrivalList))
| |--Clustered Index
Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS [t1]),
SEEK:([t1].[Apt1]=[f1].[toairport]),
WHERE:([t1].[Apt2]=[f2].[FromAirport] AND (([t1].[Term1]=' ' AND
[t1].[Term2]=' ') OR ([f1].[arrTerm]=[t1].[Term1] AND
[f2].[DepTerm]=[t1].[Term2]))) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([dohop].[dbo].[Transit].[PK_Transit] AS
[t2]), SEEK:([t2].[Apt1]=[f2].[ToAirport]),
WHERE:([t2].[Apt2]=[f3].[fromairport] AND (([t2].[Term1]=' ' AND
[t2].[Term2]=' ') OR ([f2].[ArrTerm]=[t2].[Term1] AND
[f3].[depTerm]=[t2].[Term2]))) ORDERED FORWARD)|||after reading this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
thread I think I will move to SP3.

Friday, March 23, 2012

feature?

feature?

feature?

Friday, March 9, 2012

Fastest connection method?

Wednesday, March 7, 2012

faster box but slower database

faster box but slower database

faster box but slower database

Footer rows to skip

Blog Archive

About Me