August 31, 2010 at 1:15 am
I have two big SQL Script files, one contains SQL "create table" statements for creating around 900 tables and another file contains SQL "create view" statement for creating views for above created tables. Both include GRANT statements for providing permissions to a certain "Application role" for each created table and view. The SQL script files are executed from SQL server by the stored procedure using the SQLCMD.
The tables and views are created successfully, but the whole process affects the performance of SQL server. Looking at the performance monitor the values for Physical Disk: "Avg. Disk Read Queue length" and "%Disk Read Time" are very high than expected.
1. Is there anything I can do to reduce those values or enhance performance in general?
2. On the SQL Scripts there is no "Go Statement", so the whole SQL Script is like one large transaction. Can it be any help by placing “GO Statement” after each CREATE statement? I will try to do this later but I am asking if there is any body experienced this before.
I will appreciate any input. Thanks
August 31, 2010 at 6:26 am
Are you also loading data when these scripts are run? By & large, a set of data definition language (DDL) scripts should run very quickly. Assuming no data being loaded into the tables, you're just defining the objects within the database and it ought to be a relatively painless operations. However, if there is also data manipulation language (DML) inserting data at the same time as the DDL is creating the objects, then, yeah, you'll probably see a lot of activity. If it's mixed DML & DDL, you'll probably benefit some, but not a lot, from breaking up the transactions. Otherwise, again assuming mixed DML & DDL, you need to take account that the data you're loading has to be allocated onto the disks and it's just going to take X amount of time & resources, depending on how much data it is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 31, 2010 at 7:24 am
Thanks Grant Fritchey for replying.
In fact on my script only DDL Scripts are run No DML at all, so there is no any data loading. The only thing I should mention is, there are already more than 9 millions tables already in the SQL Server, (I am not sure if that can be the reason on performance issue as I don’t know how SQL server works behind the scene on creating new tables or views).
N.B: I used the SQLCMD because I had to pass the parameter (variable) to the SQL script which adds a unique prefix to each table’s name to be created.
E.g. CREATE TABLE [dbo].[$(MyVariableName)MyTableName]
August 31, 2010 at 7:35 am
9 Million tables?
I suspect that's the issue. I've never even heard of a database with that many tables. I'm not sure what kind of problems you can expect to see there, but I'm guessing that your slow down is occurring as the system tables and indexes are updated.
That's a very large number of tables. Do you mind my asking what kind of database it is? What business is it in support of?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2010 at 2:29 am
But, it is written that the SQL Server database can handle up to 2,147,483,647 Objects. Looking at the number of objects (tables, views, stored procedures, extended stored procedures, user-defined functions, triggers, rules, defaults, and constraints) in our Database, is not even closer to that yet.
September 6, 2010 at 2:51 am
That's the physical limit, but it's not a guarantee that it will perform well up to that number.
You should have some very serious hardware specifications to expect SQL Server to cope well with such a large number of tables. It's almost certainly the number of tables that's slowing down DDL operations.
Do you mind sharing the hardware spec/overall DB size as well as answering Grant on why you need such a large number?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply