Stack Overflow
Stack Overflow

Questions tagged [sql-server-performance]

49 questions
21
votes
3 answers

Indexes with included columns, what's the difference?

I've never really understood the difference between these two indexes, can someone please explain what the difference is (performance-wise, how the index structure will look like in db, storage-wise etc)? Included index CREATE NONCLUSTERED INDEX…
dadde
  • 616
  • 1
  • 10
  • 23
11
votes
2 answers

SQL Server 100% CPU Utilization - One database shows high CPU usage than others

We have an SQL server with about 40 different (about 1-5GB each) databases. The server is an 8 core 2.3G CPU with 32Gigs of RAM. 27Gig is pinned to SQL Server. The CPU utliziation is mostly close to 100% always and memory consumption is about 95%.…
Lalman
  • 886
  • 2
  • 9
  • 25
10
votes
1 answer

SQL Server Performance Comparison Between Over Partition By And Group By

Although a couple of questions were already posted in SO about the difference between Over Partition By and Group By, I did not find a definitive conclusion about which performs better. I set up a simple scenario at SqlFiddle, where Over (Partition…
Veverke
  • 4,668
  • 1
  • 37
  • 78
8
votes
4 answers

Using GUIDs in Primary Keys / Clusted Indexes

I'm fairly well versed in SQL server performace but I constanly have to argue down the idea that GUIDs should be used as the default type for Clusterd Primary Keys. Assuming that the table has a fairly low amount of inserts per day (5000 +/- rows /…
NTDLS
  • 4,429
  • 4
  • 38
  • 67
7
votes
1 answer

Sql Server 2008 R2 DC Inserts Performance Change

I have noticed an interesting performance change that happens around 1,5 million entered values. Can someone give me a good explanation why this is happening? Table is very simple. It is consisted of (bigint, bigint, bigint, bool, varbinary(max)) I…
Falcon
  • 620
  • 1
  • 8
  • 24
6
votes
1 answer

Are there any way to programmatically execute a query with Include Actual Execution Plan and see whether any index suggestion or not

I have a quite good number of queries and i want to test each of them with Include Actual Execution Plan feature on sql server management studio However it is not possible for me to do this manually for 1m + queries So i wonder can i execute them…
MonsterMMORPG
  • 20,310
  • 69
  • 183
  • 306
6
votes
2 answers

Constant SQL Server 80% CPU Utilization

We have a small (for now) Asp.Net MVC 5 website on a dedicated VPS. When I go to the server and fire-up task manager, I see that "SQL Server Windows NT - 64 bit" is using around 80% of CPU and 170MB of RAM and IIS is using 6% CPU and 400MB of RAM.…
Alireza Noori
  • 13,898
  • 24
  • 89
  • 165
5
votes
3 answers

Optimize the application with huge number of database requests per minute

I have to provide free demo of some service to end users in my application. Free demo could be of 30 mins, 1 hours, 5 hours etc. (predefined time) for a new user for one time only. User can also consume that time in parts. like in 30 mins of free…
Jitendra Pancholi
  • 6,617
  • 8
  • 39
  • 72
3
votes
1 answer

SQL: Find missing hierarchy Folders (Paths) in a table

I have a table which contains Folders Paths. I need to find all the "gaps" between those folders in the hierarchy. I mean that, if the table contains these 3 folders: 'A' 'A\B\C' 'A\B\C\D\E\F\G' I need to find the following missing folders in the…
Nuriel Zrubavely
  • 423
  • 2
  • 12
3
votes
0 answers

Forcing bitmap filter in query plan

Is there any way to force bitmap filter operator in a three table inner join. the query is running is parallel and i have a where clause in it which filter about half of the rows from one table but i dont see a bitmap filter operator. I couldnt…
jesijesi
  • 179
  • 1
  • 7
2
votes
1 answer

SQL Server: 12% index scan density and 50% fragmentation. How bad is "bad"?

How much fragmentation is bad? How low of scan density is too low? How low does scan density is bad? i have a table with the following index density and fragmentation levels: Name Scan Density Logical…
Ian Boyd
  • 220,884
  • 228
  • 805
  • 1,125
2
votes
1 answer

Performance concern for a generic mechanism of saving files

I want to create a generic mechanism for saving files in my application and database and to do that I have come up with the idea of creating two tables with following schema in order to save files related to any row in any database…
gwt
  • 2,171
  • 4
  • 31
  • 56
2
votes
0 answers

sql server table-valued parameters stored procedure limit

I am using table-valued parameters stored procedure in SQL Server 2008/2012. In the past I had instance problems passing 50k rows (instance stop responding, memory issues) so now I limit the number of rows in tvp to 5-10k . Is there a limit for the…
giacomo23
  • 21
  • 3
2
votes
1 answer

Best way to track locks - SQL Server

We have a new sp getting released and during testing we found that when it runs its blocking other OLTP transactions. We found that initially it was because the new sp was causing lock escalation on a table and we reduced the number of batch size…
jesijesi
  • 179
  • 1
  • 7
2
votes
1 answer

different estimated rows on same index operation?

Introduction and Background I had to optimize a simple query (example below). After rewriting it several times I recognized that the estimated row count on the one and same index operation differs depending on the way the query is…
CTi
  • 23
  • 2
1
2 3 4