insert takes too long sql server 2008 r2

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi
I have a database with several tables with guid clustered column table1 have 10 rows only, table_2 have 300K rows table_2 also have 3 triggers.

I faced a very slow insert on table_2 (0.3 - 0.7) sec on very simple insert (insert into table_2 (t50) values (1))

I did the following using sql management studio :

1- set guid to a non-clustered .

2- add int unique id clustered column with auto increment.

3- drop and recreate the table (management studio did the drop and recreate when apply changes).

4- drop all triggers on table_2.

5- set database backup model to simple.

and i did shrink the database using this t-sql

ALTER DATABASE Mydatabase SET RECOVERY SIMPLE
DBCC SHRINKDATABASE (Mydatabase , 1).

insert still takes (0.3 - 0.7) second on table_2 and even on table_1

Notes:
table_1 have only 3 column and 10 rows.

hard disk drive is ssd, cpu core i7 , ram 16GB

Any idea ???!!

Posted 28-Jul-15 22:50pm

M. Rawan

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-28T23:20:00

There are a lot of possibilities. For example your hardware might be the cause so investigate using for example Windows Performance Monitor:
- disk queue length
- CPU utilization
- memory issues, especially paging.

On the other hand it might well be the database:
- what do the triggers do, long running operations there?
- how many indexes you have on the table
You can use SQL Server Trace to investigate these

And the last element is the network. If the SQL Server trace shows reasonable response time which, then investigating the network would be in place.