MS SQL Server - Do I need an extra .ndf file on locking database?

Author

Do I need an extra .ndf file on locking database?

quackhandle1975@yahoo.co.uk

2005-04-27, 9:23 am

Hi

We have a production db set up in the following configuration:

F:\Data\File1_Data.mdf - size: 61571392KB - maxsize: unlimited -
growth: 126976KB

H:\Logs\Logfile.ldf - size: 1115136KB - maxsize: unlimited - growth:
131072KB

F:\Data\View_Index.ndf - size: 2097152KB - maxsize: unlimited - growth:
10%

We have an issue with this particualar db with too many lock blocks on
it during the day as many users access this db and one very large table
with over 30million rows in it (with around 100,000 transactions per
hour. I am not sure why an extra datafile (.ndf) was added. I have
been informed me that the previous dba felt that the issues was due to
the autogrowth of the database. It may be that the locks aren't caused
by the file structure (There are two triggers on the large table and
both have cursors inside them!) However given the figures above:

1) Should I extend the growth of the .ndf file?
2) Do I need even the extra datafile?
3) Should I extend the growth of the main datafile?

Thanks

qh

Uri Dimant

2005-04-27, 11:23 am

Hi
Start with
http://www.sql-server-performance.c...se_settings.asp

Does the secondary file.ndf contains any data? It makes sense to move a
large data there.
What is recovery model of the database? If it not in SIMPLE ,do you perform
backup log file?

< quackhandle1975@yaho
o.co.uk> wrote in message
news:1114612610.529252.82630@f14g2000cwb.googlegroups.com...
> Hi
>
> We have a production db set up in the following configuration:
>
> F:\Data\File1_Data.mdf - size: 61571392KB - maxsize: unlimited -
> growth: 126976KB
>
> H:\Logs\Logfile.ldf - size: 1115136KB - maxsize: unlimited - growth:
> 131072KB
>
> F:\Data\View_Index.ndf - size: 2097152KB - maxsize: unlimited - growth:
> 10%
>
> We have an issue with this particualar db with too many lock blocks on
> it during the day as many users access this db and one very large table
> with over 30million rows in it (with around 100,000 transactions per
> hour. I am not sure why an extra datafile (.ndf) was added. I have
> been informed me that the previous dba felt that the issues was due to
> the autogrowth of the database. It may be that the locks aren't caused
> by the file structure (There are two triggers on the large table and
> both have cursors inside them!) However given the figures above:
>
> 1) Should I extend the growth of the .ndf file?
> 2) Do I need even the extra datafile?
> 3) Should I extend the growth of the main datafile?
>
> Thanks
>
> qh
>

Tibor Karaszi

2005-04-27, 11:23 am

The number and size of database size does not affect locking. Make sure you have good SQL (like
avoiding cursors etc), and support your queries with good indexes and also don't keep transactions
open for longer time than necessary.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

< quackhandle1975@yaho
o.co.uk> wrote in message
news:1114612610.529252.82630@f14g2000cwb.googlegroups.com...
> Hi
>
> We have a production db set up in the following configuration:
>
> F:\Data\File1_Data.mdf - size: 61571392KB - maxsize: unlimited -
> growth: 126976KB
>
> H:\Logs\Logfile.ldf - size: 1115136KB - maxsize: unlimited - growth:
> 131072KB
>
> F:\Data\View_Index.ndf - size: 2097152KB - maxsize: unlimited - growth:
> 10%
>
> We have an issue with this particualar db with too many lock blocks on
> it during the day as many users access this db and one very large table
> with over 30million rows in it (with around 100,000 transactions per
> hour. I am not sure why an extra datafile (.ndf) was added. I have
> been informed me that the previous dba felt that the issues was due to
> the autogrowth of the database. It may be that the locks aren't caused
> by the file structure (There are two triggers on the large table and
> both have cursors inside them!) However given the figures above:
>
> 1) Should I extend the growth of the .ndf file?
> 2) Do I need even the extra datafile?
> 3) Should I extend the growth of the main datafile?
>
> Thanks
>
> qh
>

quackhandle1975@yahoo.co.uk

2005-04-28, 9:23 am

Hi,

thanks for both your replies, I wasn't entirely convinced that extra
file was causing the locks (more likely the poor design of the db, ie -
cursors!) I was just curious as to why the db had an extra file that
was stored on the same drive, I assume it can help db performance if
heavy accessed tables are stored in a file on another drive partition.

Since the figures I gave were for a production server it is hard to
test the changes, however would it be better to grow the main database
file by 200MB rather than 124MB?

Thanks for the sql-performance article link, very helpful.

Regards,

qh

Tibor Karaszi

2005-04-28, 11:23 am

As for filegrowth, it is simple. The higher value, the longer time the grow take, higher risk for
timeout etc for those waiting for the grow etc. The smaller size, the faster it takes, but it occurs
more frequently. Only you can decide what suits you. Not that big difference between 124MB and
200MB, though... You might want to read this (apply most to shrink, but some part are relevant for
grow as well):

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

< quackhandle1975@yaho
o.co.uk> wrote in message
news:1114696375.964531.194250@o13g2000cwo.googlegroups.com...
>
>
> Hi,
>
> thanks for both your replies, I wasn't entirely convinced that extra
> file was causing the locks (more likely the poor design of the db, ie -
> cursors!) I was just curious as to why the db had an extra file that
> was stored on the same drive, I assume it can help db performance if
> heavy accessed tables are stored in a file on another drive partition.
>
> Since the figures I gave were for a production server it is hard to
> test the changes, however would it be better to grow the main database
> file by 200MB rather than 124MB?
>
> Thanks for the sql-performance article link, very helpful.
>
> Regards,
>
> qh
>

Sponsored Links