Tag: performance

SSIS 2012, reading (small) nvarchar(max), sluggish performance

I’m reading some data from a SQL Server 2012. The source table has some 600 million rows, and two of the headache columns are nvarchar(max). Problem: The performance drops from 100k rows per second to 800 per second when including these blob columns. Is there some way in make things go faster with blobs in […]

How do I make a Linq to SQL query faster that includes multiple predicate criteria?

This query is 3 times slower in execution when I execute it from the code than when I execute the equivalent SQL from SQL Server Management Studio code var myDataContext = new AccessMyDatabaseDataContext(); myDataContext.ObjectTrackingEnabled = false; var schedule = (from s in myDataContext.MyTable where s.Date >= StartDate && s.Date <= EndDate && s.Name == “MySchedule” […]

Arithmetic in SSRS calculated field vs. SQL Server stored procedure

I am designing a SSRS report that uses a stored procedure as the dataset. The stored procedure currently looks like this: SELECT sq.Name, SUM(PassPartA) AS ‘TotalPassPartA’ SUM(PassPartB) AS ‘TotalPassPartB’ COUNT(*) AS ‘TotalTests’ FROM ( — Sub-query here ) AS sq GROUP BY sq.Name The results will look something like this: +——————————————————-+ | Name | TotalPassPartA […]

SQL Server: grouping continuous value of a column

I have this view vMobileHistory_MobileRegion which is like: strUnitID strDate strTime iMobileHistory isValidRegion ————————————————————— 352848028160311 1394/11/01 10:35:16 33029937 0 352848028160311 1394/11/01 10:35:17 33029938 0 352848028160311 1394/11/01 10:35:18 33029939 1 352848028160311 1394/11/01 10:35:19 33029940 1 352848028160311 1394/11/01 10:35:20 33029941 1 352848028160311 1394/11/01 10:35:22 33029942 0 352848028160311 1394/11/01 10:35:25 33029943 0 352848028160311 1394/11/01 10:35:28 33029944 0 352848028160311 […]

sql performance degrading – loading 60,000 xml files – ssis – xml source

I have a ssis data flow task which loads xml data into sql database – more than 60,000 xml files. My first few thousands of xml files gets loaded into the table faster. But as time progresses, the loading speed is reduced drastically. first 10k files gets loaded in 10 minutes approx. next 10k takes […]

SQL SSIS Package Exec time

I have a complex SQL SSIS Package. When I execute it in Visual Studio IDE takes less than an hour to execute. After deploying it to “Integration Services Catalogs” and setup a step in schedule job to execute the same package, the execution time is 5 to 7 time longer. Why does the same package […]

SQL Server network perfomance over linked server

Here’s a tricky question: Server A (SQL Server 2016) pulls data form server B (SQL Server 2014 SP1) and C (SQL Server 2014 SP1) over linked server. Linked servers are configured using the same user, that has the same permissions on B and C servers. B and C servers has the same database with the […]

How to optimize sub folder generation for a FILESTREAM-Enabled sql server Database?

I am familiarizing with FILESTREAM-Enabled sql server Databases. Considering Microsoft’s query to create the database, it becomes clear there obviously needs to be some a folder where to put files after database creation: the filestream1 folder. Such a folder needs to be in an existing data folder CREATE DATABASE Archive ON PRIMARY ( NAME = […]

Fragmentation of one specific index is increasing too often

I have a large table and it has more than 10 indexes. I have a fragmentation problem on one specific index. In the day hours, thousands of rows are being inserted in this table and the fragmentation of just one specific index is increasing very frequently. Other indexes are OK (maybe 0.01% per hour), but […]

SQL Azure query aggregate performance issue

I’m trying to improve our SQL Azure database performamce, trying to change the use of CURSOR while this is (as everybody told me) something to avoid. Our table is about GPS information, rows with a id clustered index and secondary indexes on device, timestamp and geography index on location. I’m trying to compute some statistic […]

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