Tag: performance

SQL Server and TPC-H Table Partitioning Performance Analysis smaller partitions, fewer reads, higher cpu costs

i’m using TPC-H (SF 10) on my SQL Server 2014 database system. In order to improve query performance I decided to partition (same disk) two of the biggest tables (Lineitem and Orders) by the date column, cause many of those queries use a date range. First i decided to use a weekly partition scheme and […]

Huge performance loss with small query in Postgres (but not SQLServ)

With Postgres 9.4, I’m having a disturbing performance loss with very simple queries. This is real handicap and is blocking my migration. Note: this problem doesn’t exist this in my SQL Server 2014 DWH. The idea is easy: to count the element of dim_activity, grouping by product_key and country_key. Yet, I want to retrieve the […]

high number of reads in SQL Profiler but not when enabling Statistics

I am trying to troubleshoot a procedure that has a high number of reads in SQL Profiler, but not when I enable Statistics. It is a procedure that uses paging, and I am seeing about 17k reads on page 400 for instance, and the interesting part is that sometimes when I recompile the procedure that […]

Alteration of stored procedure containing loop is taking too long

I didn’t find any appropriate solution for my problem so I want to ask here if someone could help me. I have a stored procedure named spImportWord which downloads word files from a file location on another server to a local folder and saves values from each word file to a table in the database. […]

How to avoid sort operator in index plan

I have two tables [LogTable] and [LogTable_Cross]. Below is the schema and script to populate them: –Main Table CREATE TABLE [dbo].[LogTable] ( [LogID] [int] NOT NULL IDENTITY(1, 1) , [DateSent] [datetime] NULL, ) ON [PRIMARY] GO ALTER TABLE [dbo].[LogTable] ADD CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED ([LogID]) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] […]

SQL Server stored procedure very slow with date variable

I am running a stored procedure in SQL Server, something like: INSERT INTO #TempTable SELECT CustomerID FROM Customer WHERE DateCreated > @TimeStamp This is ignoring the index on the date column. I run the select query it is fine with a parameter, I change to this it is fine: INSERT INTO #TempTable SELECT CustomerID FROM […]

Database state between multiple applications

I have a game server that uses a SQL database to store/retrieve player data. The game server has a queue of pending database requests/queries to avoid blocking the network thread poll. If there’s too many players playing, the database gets stressed and begins slowing down queries. If I have multiple servers running, my fear is […]

heavy Weight SQL inserts (SqlBulkCopy alternative) without the use of Stored procedure

as i was testing SqlBulkCopy at first, it was a preferable way to avoid the multiple round-trips with loop of generated inserts From C# to SQL server/ but then i have discovered the user-defined table types Table-value parameter , which was the faster approach, though when working without a stored procedure, either when simply there […]

Get SQL server to query run faster

I am very new to SQL Server and finally got my code to run, but its very slow. My database is a bit on the big side (1.22mill rows, 3 columns) and I have some while loops, which I know isn’t ideal but I couldn’t find a way around it. Any help would be greatly […]

What's the most important factor for SSRS performance (Proc/Ram/IO)

I’m building a new SSRS VM in Azure. The VM will host all the application RDLs. Report execution will be done via webservice call from a cloud app. And the datasource for the reports will be a SQL Azure Database. So really all the SSRS box is doing is co-ordinating the query-exec, converts to PDF […]

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