Need urgent help. Problem with SQL and win server.

[georgesmith]

Dear members,

Please help me in regarding my problem. I am very much confused on whom to blame for the problem........MSSQL? or .NET ? or Win server?

I have a dedicated server with 2GB RAM, SQL 2005, 150GB bandwidth, 160 GB disk space etc.,

I have created a .net application that fetches data from different servers using their XML feed (wether report, stock data, etc.,). I programmed it in such a way that it gets the data using XML and stores it in a DB table. From there my website gets the data and displays it.

Now when I try to install the application on my server, the CPU usage is peaking to 100%. The task manager shows that SQL is the main cause.

I tried installing the application on my local system and connected to the dedicated server DB and the server work fine (CPU 2%). I installed the application on the dedicated server and connected it to my other SQL DB that on other server and even then it worked fine (CPU 3%).

The problem arises when I install the application on the same server where the SQL DB is.

Can anyone help me please.............I dont know what to do. I am totally confused.

Thanking you in advance,

Warm regards,
Steve

[andavari]

Sounds like you have some performance holes in SQL server 2005. In your application, you need to do some testing for queries. There are a bunch of apps you can dl for free that can do this for you. Once you figure out which query i smaking your server go haywire, you can change some SQL statements and maybe (if you have to) can do some index repair. Also, usually, I've found it tough to run a query app from the same machine since you're using a hefty amount of resources on the same box. Think of this, you have your app providing that XML feed, it pops the requests out of the SQL box and then re-routes them to its own localhost. It may just be a matter of slowdown providing asynchronous requests, posts then queries and finally self generated SQL code to update those requests for your viewing. Check the first, if that doesn't work, think of adding your app to a remote box away from that DB. By the way, how big is the DB so far?

[georgesmith]

Sir, Thank you for your reply,

My DB is not exceeding more than 30 MB of space, but there are lot of error logs files that are more thant 35GB. I have removed those log files. But still the problem persists.

I will try testing the app queries first and increasing the Indexs. By-the-way, can you please explain me how to increase the Index of each table?

The normal index is '8'. I read a lot of posts saying that increasing index will solve the problem. Can you please help me in this?

Thanking you

Yours sincerely,

Steve

[andavari]

Actually, the number of indexes doesn't increase performance; if anything, the number of indexes could possibly cause some performance issues. Number of indexes doesn't help anything. I've seen 4 TB databaes with as little as 15 indexes. Number of indexes does not apply directly to performance, it should apply to forms of data you need to represent in an index. Check out some more SQl commands on this link I've provided and those commands should help you optimize the database. You just need an optimization, not an increase in overall index parameters. If anything, since your database is so small, what you should focus on in your application is directing your application to what exactly it should download from that XML feed. Have your application use some discriminant load parameters. Let me know how it turns out...

Sure thing, here's a tip from a website I use a lot:

http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx

Tips for Rebuilding Indexes
By : Brad McGehee
Feb 21, 2007 Printer friendly

Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).

The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:

--Script to automatically reindex all tables in a database

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.

For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized. [7.0, 2000, 2005] Updated 7-24-2006

*****

When you create or rebuild an index, you can specify a fill factor, which is the amount the data pages in the index that are filled. A fill factor of 100 means that each index page is 100% full, a fill factor of 50% means each index page is 50% full. If you create a clustered index that has a fill factor of 100, and it is not based on a monotonically increasing key, that means that each time a record is inserted (or perhaps updated), page splits may occur because there is no room for the data in the existing pages. Numerous page splits can slow down SQL Server's performance.

Here's an example: Assume that you have just created a new index on a table with the default fill factor. When SQL Server creates the index, it places the index on contiguous physical pages, which allows optimal I/O access because the data can be read sequentially. But as the table grows and changes with INSERTS, UPDATES, and DELETES, page splitting occurs. When pages split, SQL Server must allocate new pages elsewhere on the disk, and these new pages are not contiguous with the original physical pages. Because of this, random I/O, not sequential I/O access must be used to gather the data, which is much slower, to access the index pages.

So what is the ideal fill factor? It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:

Low Update Tables (100-1 read to write ratio): 100% fill factor
High Update Tables (where writes exceed reads): 50%-70% fill factor
Everything In-Between: 80%-90% fill factor.
You may have to experiment to find the optimum fill factor for your particular application. Don't assume that a low fill factor is always better than a high fill factor. While page splits will be reduced with a low fill factor, it also increases the number of pages that have to be read by SQL Server during queries, which reduces performance. And not only is I/O overhead increased with a too low of fill factor, it also affects your buffer cache. As data pages are moved in from disk to the buffer, the entire page (including empty space) is moved to the buffer. So the lower the fill factor, the more pages that have to be moved into SQL Serve's buffer, which means there is less room for other important data pages to reside at the same time, which can reduce performance.

If you don't specify a fill factor, the default fill factor is 0, which means the same as a 100% fill factor, (the leaf pages of the index are filled 100%, but there is some room left on intermediate index pages). In most cases, this default value is not a good choice, especially for clustered indexes. [6.5, 7.0, 2000, 2005] Updated 7-24-2006

*****

If you find that your transaction log grows to an unacceptable size when you run DBCC REINDEX, you can minimize this growth by switching from the Full Recovery mode to the Bulk-Logged mode before you reindex, and when done, switch back. This will significantly reduce the size of the transaction log growth. [2000, 2005] Updated 7-24-2006

*****

If you have a table that has a clustered index on a monotonically increasing or decreasing primary key, and if the table is not subject in UPDATEs or if it has no VARCHAR columns, then the ideal fill factor for the table is 100. This is because such a table will normally not experience any page splits. Because of this, there is no point in leaving any room in the index for page splits. And because the fill factor is 100, SQL Server will require fewer I/Os to read the data in the table, and performance will be boosted. [7.0, 2000, 2005] Updated 7-24-2006

*****

If you are not sure what to make the fill factor for your indexes, your first step is to determine the ratio of disk writes to reads. The way to do this is to use these two counters: Physical Disk Object: % Disk Read Time and Physical Disk Object: % Write Time. When you run both counters on an array, you should get a good feel for what percentage of your I/O are reads and writes. You will want to run this over a period of time representative of your typical server load. If your percentage writes greatly exceeds the percentage of reads, then a lower fill factor is called for. If your percentage of reads greatly exceeds the percentage of writes, then a higher fill factor is called for.

Another Performance Monitor counter you can use to help you select the ideal fill factor for your environment is the SQL Server Access Methods: Pages Splits/Sec. This counter measures the number of page splits that are occurring in SQL Server every second. For best performance, you will want this counter to be as low as possible, as page splits incur extra server overhead, hurting performance. If this number is relatively high, then you may need to lower the fill factor in order to prevent new page splits. If this counter is very low, then the fill factor you have is fine, or it could be a little too low. You won't know unless you increase the fill factor and watch the results.

Ideally, you want a fill factor that prevents excessive page splits, but not so low as to increase the size of the database, which in turn can reduce read performance because of all the extra data pages that need to be read.

Once you know the ratio of disk write to reads, you now have the information you need to help you determine an optimum fill factor for your indexes. [6.5, 7.0, 2000, 2005] Updated 7-24-2006

*****

If you want to determine the level of fragmentation of your indexes due to page splitting, you can run the DBCC SHOWCONTIG command. Since this command requires you to know the ID of both the table and index being analyzed, you may want to run the following script:

--Script to identify table fragmentation

--Declare variables
DECLARE
@ID int,
@IndexID int,
@IndexName varchar(128)

--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = OBJECT_ID('table_name') --enter name of table

--Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

--Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)

While the DBCC SHOWCONTIG command provides several measurements, the key one is Scan Density. This figure should be as close to 100% as possible. If the scan density is less than 75%, then you may want to reindex the tables in your database, and also increase the fill factor if you are finding that the current fill factor you are using is not appropriate. [6.5, 7.0, 2000] Updated 7-24-2006

*****

Here's a script that is used to create DBCC SHOWCONFIG commands for all of the indexes in one or more tables. Once you run this script, it will produce for you a DBCC SHOWCONFIG statement for each index, which you can then run to find out about the level of fragmentation of your indexes. This script is especially handy if you don't know the names of the indexes in your tables (which is most of the time).

SELECT 'dbcc showcontig (' +
CONVERT(varchar(20),i.id) + ',' + -- table id
CONVERT(varchar(20),i.indid) + ') -- ' + -- index id
object_name(i.id) + '.' + -- table name
i.name -- index name
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = 'U'
and i.indid < 2
and
i.id = object_id(o.name)
ORDER BY
object_name(i.id), i.indid

Once you run this script, the output will be DBCC SHOWCONFIG statements for each of the tables(s) and index(es). This output can then be cut and pasted into Query Analyzer or Management Studio and run, which produces a DBCC SHOWCONFIG result for every index for every table you specified. [7.0, 2000, 2005] Updated 11-1-2005 Tip contributed by Jeff M. Belina and Steven R. Morrow