Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

The database is end of day data storage with some reporting.

We fill db with data for a particulate date every day.

Time to time users' reports block import processes (run from C# apps and then with sprocs processing data on the SQL side).

So my idea is to separate reporting from storing into 2 separate dbs.

Would you agree?

If that I need a replication. What would be the recommendation?

I can get another box in the same data centre. The amount of data is about 2Gb a day.

I wont get 3d box so the SSRS should be installed on top of the 'reporting' db.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

It took sometime to understand the problem. Correct me if I am wrong.

You have a database where you load data for only one data and users ask for reports on this data. But during the import process (done only once a day) the reports are being blocked. The amount of data being added is 2GB everyday. And you want to optimize this process.

What is the SELECT @@VERSION? I know you are using 2008 but I need the edition info also. Do you append 2 GB of data or truncate and load only 2GB every day? How much memory is available on the box? Can you share the info from the below query too.

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

SQL Server is very powerful and can get a lot of performance from it when used correctly. We don't have enough information to make a judgement now. You may need REPLICATION or may be you can just improve the performance by tuning performance, adding appropriate indexes and throwing little memory too. So please add more details.

share|improve this answer
    
10.0.2776.6 Enterprise x64. 12Gb is allocated for the SQL. I cant add memory unfortunately. Also I cant run the query you gave me as I dont have admin rights and the DBAs are very busy and you can get their attention only if something is failing. The data is not truncated but I run updates statistics on tables I import to every day. The real problem is that blocked processes do not progress they just block for as long as get killed. If say import takes 10 min and report takes 2 min then running 2 processes should take 12 min if run at the same time. however they just stuck –  Boppity Bop Apr 9 '11 at 13:15
    
From the info you shared, it looks like a case for not having appropriate indexes in place for me. Look at the missing indexes DMV. Also, are you updating stats with fullscan or with default sample. That also makes difference depending on your data. When the processes are being blocked, have you looked at on what they are being blocked? Try sp_whoisactive monitoring procedure from Adam Machanic. –  Sankar Reddy Apr 9 '11 at 15:00
    
thanks it seems I have to make the DBAs look into it. too much for a dev. –  Boppity Bop Apr 10 '11 at 18:52

If you want to use that database for reporting only, then you should consider using logshiping with read only mode.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.