Change of Execution plan

Hi,

We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.

Need help on this, otherwise it seems we might have to move back to 2000.

Thanks in Advance
Ritesh

Did you update statistics after you upgraded? It seems unlikely to me that SQL 2005 would provide a less efficient execution plan than SQL 2000, given accurate statistics data. I'd update statistics and then recompile all the stored procedures first. I'd also be interested to see what you had to tune in the stored procedure you changed to accommodate SQL 2005.|||

Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.

Change of Execution plan

Hi,

We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.

Need help on this, otherwise it seems we might have to move back to 2000.

Thanks in Advance
Ritesh

Did you update statistics after you upgraded? It seems unlikely to me that SQL 2005 would provide a less efficient execution plan than SQL 2000, given accurate statistics data. I'd update statistics and then recompile all the stored procedures first. I'd also be interested to see what you had to tune in the stored procedure you changed to accommodate SQL 2005.|||

Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.

Change minimum database size from 3mb to 2mb

Hi
I have been using sql express so far with my FileManager database.
Since I migrated to SQL 2005 developer edition, I am unable to execute
sql script created from FileManager database before uninstalling sql
express.
Code:
USE [master]
GO
/****** Object: Database [FileManager] Script Date: 11/14/2007
19:58:28 ******/
CREATE DATABASE [FileManager] ON PRIMARY
( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
..
..
..
Error:
Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at
least 3 MB to accommodate a copy of the model database.
How do i correct that.
Regards
Piotr Kolodziej
Hello,
You can not create database files less than your model database's database
files.
By default, model database has a 3MB in size mdf file and 1MB in size ldf
file. So you can not create a data file (mdf) less than 3MB in this
situation. If you want to
create a 2MB in size data file, then go and change your model databases mdf
data file size and set it 2MB. Then you'll be able to run your following
code successfully.
Ekrem nsoy
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej

Change minimum database size from 3mb to 2mb

Hi
I have been using sql express so far with my FileManager database.
Since I migrated to SQL 2005 developer edition, I am unable to execute
sql script created from FileManager database before uninstalling sql
express.
Code:
USE [master]
GO
/****** Object: Database [FileManager] Script Date: 11/14/2007
19:58:28 ******/
CREATE DATABASE [FileManager] ON PRIMARY
( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
.
.
.
Error:
Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at
least 3 MB to accommodate a copy of the model database.
How do i correct that.
Regards
Piotr KolodziejThe error message tell you what the problem is. The model database determine
s the minimum database
size. The size of model in 2005 (data file) is 3MB, which is why you can't m
ake the new database
smaller. You could shrink model first, but I do not recommend that solution.
Amend your script
instead and make the new database a reasonable size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej|||Hello,
You can not create database files less than your model database's database
files.
By default, model database has a 3MB in size mdf file and 1MB in size ldf
file. So you can not create a data file (mdf) less than 3MB in this
situation. If you want to
create a 2MB in size data file, then go and change your model databases mdf
data file size and set it 2MB. Then you'll be able to run your following
code successfully.
Ekrem nsoy
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej

Change minimum database size from 3mb to 2mb

Hi
I have been using sql express so far with my FileManager database.
Since I migrated to SQL 2005 developer edition, I am unable to execute
sql script created from FileManager database before uninstalling sql
express.
Code:
USE [master]
GO
/****** Object: Database [FileManager] Script Date: 11/14/2007
19:58:28 ******/
CREATE DATABASE [FileManager] ON PRIMARY
( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
.
.
.
Error:
Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at
least 3 MB to accommodate a copy of the model database.
How do i correct that.
Regards
Piotr KolodziejThe error message tell you what the problem is. The model database determines the minimum database
size. The size of model in 2005 (data file) is 3MB, which is why you can't make the new database
smaller. You could shrink model first, but I do not recommend that solution. Amend your script
instead and make the new database a reasonable size.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej|||Hello,
You can not create database files less than your model database's database
files.
By default, model database has a 3MB in size mdf file and 1MB in size ldf
file. So you can not create a data file (mdf) less than 3MB in this
situation. If you want to
create a 2MB in size data file, then go and change your model databases mdf
data file size and set it 2MB. Then you'll be able to run your following
code successfully.
--
Ekrem Önsoy
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej