Tagged Questions
0
votes
3answers
47 views
Dropping and recreating indexes
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 ...
5
votes
1answer
61 views
Troubleshooting SOS_SCHEDULER_YIELD wait
Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems.
After performing the same activities in both the development and ...
2
votes
2answers
55 views
Performance effect of indexing foreign keys
My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.
There are two tables involved. The first is a ratings table with 2 columns: ...
0
votes
0answers
38 views
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]
The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
7
votes
4answers
310 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 ...
2
votes
1answer
65 views
excessive CPU utilization by SQL server
I have an issue with SQL server 2000. Its CPU utilization is high from 9.00 AM to 5.00 PM. Three applications use this server through three different instances but when the CPU utilization is high one ...
1
vote
3answers
71 views
Improve performance by partitioning
I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.
...
3
votes
0answers
101 views
Where and how to store my weird datamart
I could really use some help here.
Here's my situation.
I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
2
votes
2answers
103 views
Is there a difference in performance between @date and getdate()?
Usually I use the getdate() function in my where clauses to go back in time.
Something like:
DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())
Will SQL Server 2008R2 perform faster ...
3
votes
1answer
54 views
CPU column 5x higher on one machine vs another?
We're running into a strange issue in a deployed version of our product where the performance is just not very good. When we restore a backup of their DB on one of our machines, it performs as we ...
1
vote
4answers
142 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 ...
1
vote
1answer
74 views
How to enhance the SQL query performance for thousands and millions of huge data
Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers ...
3
votes
2answers
136 views
Why use separate databases for high traffic/volume tables?
While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things.
The first one contains the ...
4
votes
1answer
92 views
.mdf vs .ldf Performance considerations
I just learned from an answer to this question that the .mdf and .ldf files that SQL Server uses are an important pair for the database. I previously thought the .ldf was just a non-critical log for ...
5
votes
3answers
155 views
How to optimize a query that's running slow on Nested Loops (Inner Join)
Consider the following simple query (only 3 tables involved)
SELECT
l.sku_id AS ProductId,
l.is_primary AS IsPrimary,
v1.category_name AS Category1,
...
2
votes
2answers
70 views
Linked Server Risks
I'm implementing a new feature which requires data from databases on multiple servers. I just need to union data from all these servers and sort it. The two options that come to mind are:
Use linked ...
0
votes
2answers
49 views
Reclaiming space from a large partitioned database
We have a database where one of the business group decided to remove several millions of rows as part of their upgrade process. (we removed for them..).. This database is partitioned based on a date ...
1
vote
2answers
86 views
sql server 2008 execution plan different on two production servers
We have two servers running SQL server 2008 R2, with identical databases, where we implemented a datawarehousing solution. The cube processing operation was taking a VERY long time in one of the ...
2
votes
3answers
126 views
SQL server disk write queue dramatically grows sometimes
I have SQL Server 2008 and a single database with full recovery model. Usually, the queue length is less than 1. But sometimes it grows up to several thousands (3000!!) for a few seconds. At this time ...
0
votes
2answers
73 views
Why does using a variable make a difference in performance?
I have a table that constantly doing insert, and I am trying to run a delete script to remove old record.
The delete script looks like this:
DECLARE @StarDate DATETIME = '2013-01-13';
DECLARE ...
2
votes
2answers
106 views
Bandwidth comparison between log shipping and transactional replication
Which technique uses more network bandwidth:
Log shipping
Transactional Replication
Can some one share any benchmarks for the same ?
What would be the Memory and I/O impact on the Primary server ...
3
votes
1answer
103 views
Why is a hash match operator in this very basic query
I'm beginning to learn some about looking at execution plans and making queries more efficient
Consider these two basic queries
select distinct pat_id, drug_class, drug_name from rx
select pat_id, ...
-8
votes
2answers
136 views
How can I check for SQL Server performance problems using T-SQL? [closed]
I know how to use the Profiler, Performance Monitor, and Activity Monitor. I want to find a way to troubleshoot performance using T-SQL. How can I check for Microsoft SQL Server performance problems ...
2
votes
6answers
390 views
SQL Server split mdf into multiple files
I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and ...
3
votes
1answer
59 views
Sql server snapshot isolation overhead when read_committed_snapshot is enabled
When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation?
Snapshot ...
3
votes
1answer
111 views
SQL Server Performance Counters Relating to Disk Access and Log Files That Appear Concerning
I want to validate the optimal performance of our SQL Server and in doing so, I have gone through all the performance counters listed at the following link and create a trace log to analyze the data.
...
1
vote
3answers
142 views
When to periodically RESTART SQL SERVER 2008 R2? [closed]
So I've read that it's really a bad practice to restart SQL Server on a production environment..
Does restarting SQL Server speed it up?
Automatically restart SQL Server 2008
Why are periodic ...
3
votes
2answers
83 views
SQL server performance and tuning
I am working on a system, there is a cisco load balancer that can handle 40K requests.
There are two app servers. There are 12 or so web services.
and there is a SQL server with 2TB of data.
...
3
votes
2answers
69 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
0answers
164 views
Efficiently retrieve big amount of data based on date range
Table EVENTS
TimeStamp |O.Name |Location |Source |
2008-11-12 |Pretorian|Európe |England |
2009-12-24 |Lex Rosa |Európe |Italy |
2010-01-01 |Nasdaq |USA |Whasington |
...
1
vote
1answer
74 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 ...
1
vote
3answers
134 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 ...
9
votes
4answers
1k views
SQL Server 2012 slower than 2008
I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
13
votes
4answers
454 views
Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
3
votes
0answers
74 views
What utilities can I use to simulate I/O load for a DB restore and DBCC check?
I'm trying to figure out the peak load I can sustain on new hardware I procured for validating backups and performing DBCC checks. I've been using Crystal Diskmark to get throughput stats which helped ...
2
votes
1answer
95 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 ...
3
votes
4answers
333 views
SQL Server - Separating data, log, and TempDB files on a SAN
I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
4
votes
1answer
124 views
Stored procedure performance metrics
I am taking over a new project which is having ~2500 SPs! and would like to know at current state what is the maximum / minimum time taken by each of the stored procs. Also IO, Logical Reads used by ...
1
vote
1answer
112 views
SSD for production database
I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts & large selects) per second. Besides ...
2
votes
1answer
239 views
SQL Server manage indexes
Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
2
votes
1answer
104 views
How much overhead is involved with querying a DB on the same server?
History
The company I work for had two databases, DB A and DB B, on two different servers. A contains information pulled from a COTS application database in addition to information from our own ...
1
vote
3answers
129 views
SQL Server 2000 performance
I have a SQL server with about 150 tables and the 5 tables that are used the most consist of over 10 million rows each. I'm trying to see why the performance of the server has been diminishing day by ...
1
vote
1answer
98 views
How does disabling a network protocol affect SQL server performance
The SQL server 2000 process info monitor displays about 100 processes that use a mix of Named Pipes/TCP-IP and (integrated authenticated) users. I was wondering if:
disabling Named pipes - or - ...
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
579 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 ...
2
votes
3answers
149 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 ...
5
votes
3answers
272 views
Reducing the size of a database
SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff ...
7
votes
3answers
274 views
Performance benefit of taking unused databases offline
I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to ...
3
votes
1answer
615 views
Should I rebuild index after truncate/large insert?
I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)
Basic outline is very simple:
...
2
votes
1answer
189 views
exist (select * from my table)
My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.
Query (1):
IF ( SELECT COUNT(UnitTrustCounterId)
FROM ...
