Tagged Questions

-1
votes
4answers
68 views

Dropping and recreating indexes [closed]

I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness). I somehow manage to rebuild ...
1
vote
4answers
147 views

Speed up large clustered index creation?

I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB. And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit ...
3
votes
2answers
70 views

Load Testing Crash Course

Can someone point me at a good resource to get a crash course in load testing. I have a sql server machine that I need to load test but I havent been thruogh the process before. Also we do not have ...
1
vote
3answers
135 views

SQL Server xml performance

I have a table with 21 columns. The majority are numerical and the rest are nvarchar. I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from ...
2
votes
1answer
96 views

Log all than queries taking more than X seconds

I have a database Microsoft SQL Server 2008r2. I have some applications in different programming languages. I would like to log all the queries that take more than X seconds. I guess that the I ...
1
vote
4answers
303 views

Update many-to-many relationship table

I have three tables: task, taskmembers, members. Each task may have up to five people associated to it. Currently, when someone updates a task I delete all the current members and insert them as a ...
5
votes
2answers
585 views

SQL Server gets slow over time until we have to restart it

We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
1
vote
3answers
605 views

How much do foreign keys affect performance?

I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign ...
3
votes
1answer
296 views

Is a clustered index locked while a (clustered index) scan is in progress?

The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this. The problem leading me to this question is that I have a view joining several ...
10
votes
1answer
451 views

SQL Server - Anyone use SUMA, trace flag 8048, or trace flag 8015?

Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server 2008 R2 system. Interested to hear from others who have found usage cases where ...
2
votes
2answers
481 views

Fast XML, slow XML

Our database update Windows application needs to transfer some data between two databases as part of the process for a certain one-time update. I chose XML as the intermediary to move the data. The ...
1
vote
3answers
78 views

Generally speaking, what pecking order should be used when tuning SQL Server?

When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to ...
2
votes
1answer
344 views

Prevent a single query from hogging all resources

I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server? The ...
6
votes
1answer
516 views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
2
votes
1answer
656 views

Connection to localhost dramatically slower than to network server

I have an Excel VBA application that connects to a SQL Server 2008 R2 database through ADODB. When I'm on the network at work, the connection is almost instantaneous. When I am at home working with ...
8
votes
3answers
2k views

How to boost performance of virgin queries in MS SQL Server?

I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
3
votes
2answers
138 views

How do I count rows with two properties in one index scan in SQL?

I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it. I need to output the following: for each day I need to ...
1
vote
3answers
196 views

Checking if the query is made async or sync on SQL Server 2008 R2

I have tried to make async calls to my SQL Server instance as you may see on my SO post: ASP.NET MVC 4 asyncronous database call: never returns the HTTP response I did a benchmarking and the sync ...
1
vote
2answers
405 views

SQL Server 2008 R2 Express high insert slowdown possibly due to PAGEIOLATCH_SH

I have a program that insert row to a table though multiple connections. For each insert, the connection would open and then close. When I run the program, the insert were fast. However, once it reach ...
3
votes
1answer
3k views

SQL Server Activity Monitor - Resource Waits - Logging > 1400 ms/sec, how?

I have an SQL Server 2008 R2 server. I open "Activity Monitor", and inside the "Resource Waits" category, I see that the "Logging" category is taking up more than 1400 ms/sec. That doesn't seem to be ...
1
vote
1answer
124 views

How should I distribute my tables for better performance from fragmentation point of view

I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously undergoing INSERTs AND DELETEs. There are about 400,000 records @ 4KB/record being inserted or deleted ...
0
votes
0answers
31 views

Fix missing performance counters [duplicate]

Possible Duplicate: How to revive SQL Server 2008R2 performance counters? On one machine, I have an instance of SQL Server 2008R2 Enterprise, Visual Studio 2010 and VS 11 Developer Preview. ...
1
vote
1answer
62 views

Reading and writing the common data

I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application ...
6
votes
4answers
410 views

Four db servers all identical, one is much slower than the others

The setup We have an application loading a lot of transaction data for later querying. It's sharded on 4 SQL Server 2008R2 Enterprise Edition on Windows Server 2008 64 bits. All servers are ...
2
votes
2answers
297 views

Performance gain by moving a large table from a database and referencing with a synonym

We have SQL Server 2008 R2 database which is currently about 80Gb. About 10Gb of this is down to one table, and I wondered if there would be a performance gain if I moved this table into it's own ...
11
votes
2answers
322 views

To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS

I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
12
votes
3answers
818 views

Slow deletion of records when a trigger is enabled

Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve. http://support.microsoft.com/kb/2606883 Ok so I have an ...
2
votes
1answer
454 views

Replication slows down on massive deletes

First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue. We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so ...