Tagged Questions
7
votes
4answers
620 views
Index Seek vs Index Scan
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.
What is the difference between and index seek and an index ...
1
vote
1answer
76 views
Consolidating indexes
I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
4
votes
1answer
152 views
One CPU in a 32-core system is getting to 100% usage and hurting performance
I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives...). Performance Dashboard is not showing blocks or deadlocks and ...
2
votes
3answers
163 views
Performance impact of Splitting Table across files
EDIT: I think the original question (below) was not worded very well, and probably caused confusion. The basic question is:
If I have a SQL Server 2005 database using a single .mdf file and I change ...
3
votes
1answer
213 views
SQL Server Row Count sp_spaceused rows discrepancy
I am researching a performance problem with an application running on SQL Server 2005. I've conducted performance tuning, and I realize that a key table is poorly structured(poorly chosen clustered ...
5
votes
3answers
303 views
Query performance degrades with time and use
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
6
votes
1answer
234 views
Backup to three drives suddenly slow
The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit ...
5
votes
2answers
256 views
Performance impact of waiting on a service broker message
I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
1
vote
1answer
184 views
Using sys.dm_os_performance_counters and DIFF to display logins per second
PROBLEM
Running my script below i can see the number of logins as an accumulated number to my instance:
SELECT cntr_value AS [LoginsPerSec]
FROM sys.dm_os_performance_counters
WHERE
...
8
votes
1answer
1k views
SQL Server TempDB behaviour in large memory environment
Reading over In-memory SQL Server log file reminded me of a question I had a short while ago.
We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
7
votes
2answers
165 views
High rise in SQL Server CPU after adding a DB
I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005).
My average CPU was on 48% ...
3
votes
3answers
620 views
What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?
I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some ...
3
votes
3answers
238 views
Performance of Inline-TVF vs. Views
I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
1
vote
2answers
292 views
Slow queries on SQL Server [closed]
We have SQL Server 2005. Our main table is the archive table which has nearly 200 million rows in it. There are 2000 clients that connect a service so the service writes the information to the ...
4
votes
4answers
204 views
Can I reorganise index on a sql server 2005 database without performance hit on production server
I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. ...
14
votes
5answers
4k views
What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?
I'm in a situation where I want to get the minimum value from of 6 columns.
I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
1
vote
1answer
185 views
What is connection time?
We are currently trialling ManageEngine SQLDBManager Plus. On the dashboard for a server it shows connection time in milliseconds (currently 15ms).
Can anyone tell me what Connection Time represents ...
8
votes
2answers
324 views
Strategy to handle an SQL Server DB with too many files (BLOBs) in it?
Scenario:
SQL Server 2005 database servicing an ASP.NET application (on a separate web servers).
Database:
DB has about 5GB of "normal" data in it, and about 15GB of "files" (e.g.: a 200k PDF stored ...
1
vote
1answer
142 views
Hard Drive Configuration for my setup
I have a fairly large transactional database (100GB) with a lot of users. The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun ...
3
votes
2answers
964 views
How badly do SQL Compilations impact the performance of SQL Server?
I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so.
I whipped out SQL Profiler ...
1
vote
1answer
213 views
What can cause an UPDATE statement to execute endless?
I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night.
Today it started but never endet.
So i've tried to run ...
3
votes
2answers
150 views
Reinitializing Subscription Performance
I have 4 Servers, Servers A, B, C, D.
Server A publishes a Replication instance, and Servers B, C, D have Pull Subscriptions to it.
Server B's subscription needs to be Reinitialized, but I am ...
13
votes
5answers
2k views
Why are periodic restarts required to keep my instance performing well?
We've got a production DB server on SQL 2005. Everything runs normally for a while, but after a couple weeks we see a notable performance drop. Only restarting SQL Server brings performance back to ...
2
votes
1answer
301 views
Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame
System:
I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total).
Issue:
During the Index Rebuild (~20 minutes), we fail to insert data into the DB
...
5
votes
3answers
380 views
SQL Server 2005 uses 1GB of RAM
Is this normal? To be exact it uses 1,068,000 KB. It occasionally shoots up to 1,400,000 if query takes too long.
My total system memory is 3GB and I am running WinXP. Is there a specific amount of ...
2
votes
2answers
161 views
Does the number of indexes effect the modification of table?
First of all Good morning !
Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !!
Now come to the point,
...
7
votes
2answers
1k views
INSERT performance difference between temporary tables and table variable
I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.
This is the code to insert ...
3
votes
1answer
743 views
How to find the detail cpu high process name which occupying most resource in the SQL Server database?
I'm running into a problem when using the following SQL to identify CPU usage in the production SQL Server 2005 env.
DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, ...
3
votes
1answer
132 views
Possible to add KEEPFIXED PLAN option to reduce the recompile in SqlServer2k5 on Production Env?
I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env?
I'd know if we may gain performance after that or on the other hand it will be ...
1
vote
4answers
333 views
Top 2 rows per partition from an absolute value date difference
I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images.
Location + Camera + image capture_date is the ...
8
votes
3answers
1k views
What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?
I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better:
delete rows and insert new ones, or
update already existing rows
For me ...
1
vote
2answers
584 views
Solving high page load time using SQL Profiler
I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what ...
3
votes
3answers
3k views
How to avoid index scans in SQL Server 2005?
I have a table named Cell which has columns like Cell_ID, CellValue and CellRow.
Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows.
...
1
vote
2answers
737 views
How to troubleshoot SQL “The timeout period elapsed prior to completion of the operation or the server is not responding.”
I'm trying to troubleshoot a timeout issue that occurs on one of our SQL 2005 servers.
The database responds normally for about a day until we start to see sql timeout errors. After that all ...
7
votes
4answers
576 views
DDL contention on TempDB
I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
2
votes
2answers
488 views
SQL 2005- stored procedure performance troubleshooting
I am investigating performance issues for a stored procedure on SQL 2005 server.
This is how I intend to do it:
Analyze the stored procedure for any bad TSQL practices (like count * etc)
Run it ...
3
votes
1answer
98 views
Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view?
Sorry for the long question, but let me give you my scenario. I have a DB that houses data exported from another DB. The containing tables are copies from the other (non-SQL) database. Each column ...
3
votes
2answers
448 views
Will creating partitions reduce locking and how do we implement this in sql-server?
A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.
This application generates quite a lot of data that is used for reporting. Before ...
2
votes
1answer
428 views
Optimising a Database Shrink (in terms of speed and reducing fragmentation)
We have a large database that has had a lot of older data stripped out, leaving primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb ...
5
votes
5answers
628 views
Advice with date column SQL Server 2005
I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example):
WITH Dates AS (
...
3
votes
2answers
772 views
SQL Server query result transfer time over VPN (WAN)
I have a mysterious phenomenon on one remote SQL Server 2005 which I manage and query using SQL Server Management Studio. I connect to the server through a VPN tunnel (provided by an ISP, about 8 ...
1
vote
0answers
149 views
SQL Server and Perfmon [duplicate]
Possible Duplicate:
SQL 2008 Server agent cant see perfmon counters
I'm trying to setup SQL Server Perfmon counters on a Windows Server 2003 with SQL Server 2005 SP3. I'd like to run ...
1
vote
2answers
189 views
dm_db_missing_index_details - does the order of equality_columns matter
I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view.
MSDN says that this ...
3
votes
1answer
498 views
Need help with ASYNC_NETWORK_IO
We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here.
The ...
3
votes
1answer
286 views
Sql Server Performance Degradation in moving from 2005 to 2008
I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. ...
3
votes
1answer
1k views
sqloledb vs sqlncli - Performance difference or just feature set?
I've been running through some Classic ASP code recently and was wondering if there was a performance difference between connecting using:
...
7
votes
4answers
3k views
TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately
I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters.
Here is my take on it:
If I am seeing a lot of SQl compilations, then ...
3
votes
2answers
276 views
Comparing two XML query plans
Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
6
votes
2answers
695 views
Displaying query plans in Server Management Studio
Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset:
select top 10
sum(qs.total_worker_time) as total_cpu_time,
...
7
votes
3answers
2k views
SQL Server performance sudden degradation
I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
