Tag: performance

SQL Server performance fast only when refresh the stored procedure

I can run a stored procedure multiple times and it wont hit it’s cache: (1665ms is duration column) But if I then alter the stored procedure changing nothing: (240ms is duration column) Problem: how to get the stored procedure to always be fast (on the second and next calls)

SQL Server Connected to Hadoop – Thoughts and Challenges of Implementation

I wanted to broach the issue of SQL Server’s Hadoop distribution called HDInsight. Given that there is a connection provided to Hadoop, does anyone have experience with HDInsight and particularly a comparison between the Hadoop / SQL Server connector and HDIinsight / SQL Server from a real life DTP scenario or personal 1 node installation? […]

Profiling and output caching in ASP.NET MVC

So I was recently hired by a big department of a Fortune 50 company, straight out of college. I’ll be supporting a brand new ASP.NET MVC app – over a million lines of code written by contractors over 4 years. The system works great with up to 3 or 4 simultaneous requests, but becomes very […]

Putting clustered index on a join used column vs heavily scanned column?

I have this simple table : Table Users userId | name ——————— 1 ‘a1’ 2 ‘a2’ 3 ‘a3’ 4 ‘a4’ 5 ‘a5’ Table Cities cityId | name ——————— 1 ‘c1’ 2 ‘c2’ 3 ‘c3’ 4 ‘c4’ 5 ‘c5’ Each user is can be in more than one city. : So the mapping table is : […]

How is SQL query processed in this example?

Purpose of this question is to find out how the inner join is used to filter out the result. I have 2 tables, related by one column. Table Names contains about 100,000+ records. Table: Names +—-+——+ | ID | Name | +—-+——+ | 1 | A100 | | 2 | A100 | | 3 | […]

Is it performant to put all Log tables into a different database catalog in SQL Server?

Supposing we have a web application, which uses a SQL Server 2005 server database, would it be better for performance to move all our custom Log tables to a specific catalog? Scenario Our web application today uses different catalogs from SQL Server. Each catalog have tables related to a problem (domain/subject): db_financial, db_corporative, etc. These […]

Is there a way to tuneup a complex count query against heavy data?

There are two tables one is empl having 545405 records and second is pam having 1466320 records. The task is to find the count of pID according aID. So to acomplish this task I write the following query. Select pa.aID, count(pa.pID) from empl join pam pa ON empl.pID = pa.pID Group by pa.aID The indexes […]

Clustered Index Descending for Paging

I have a screen where i show all the latest posts and when the user click the read more link, i bring the next set based on Post Id(where id < latest one already shown). I have a clustered index ascending on field Id on Posts table. I was wondering if i am always showing […]

How to speed up SQL Server query with grouping and filtering

I have quite common task to show history of sales in our web application (asp.net + mssql). I have a table with sale transactions like: – SellerID (string) – Product PartNumber – Product ManufacturerName – ProductID (string uniq normalized PN+MN) – Date of sale – Price – Qty – Option 1 – Option 2 – […]

There are about 70 million inserts in a week.What indexes should i use on my columns?

I have got a table in sql server 2012 which gets almost 66 million inserts in a week. On which columns should I use indexes? create TABLE [dbo].[Details]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [WorkflowContentMetricsID] [bigint] not null, [ContentType] [nvarchar](1000) NULL, [SourceID] [nvarchar](2000) NULL, [DestinationID] [nvarchar](2000) NULL, [ParentSourceID] [nvarchar](2000) NULL, [Title] [nvarchar](max) NULL, [Status] [nvarchar](max) NULL, […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.