Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Million of Records
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thisisgerald
Starting Member

Philippines
4 Posts

Posted - 01/06/2015 :  01:02:26  Show Profile  Reply with Quote
Hi Admin/Gurus,

This is my first post here. :)

Would like to get comment/feedback on my case.
User execute a program with select query which
will have a result of 2-3 million of records.

Randomly, another user will experience problem creating data
using another program. They access the same database but different
tables. Do the resources used by the program with select query
can be used by another select/insert/update query?

Thank you in advance!

Best Regards,
thisisgerald

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 01/06/2015 :  01:49:34  Show Profile  Visit jackv's Homepage  Reply with Quote
Even though the two programs are using different tables , they are still sharing resources , such as memory, disk , CPU , etc.
In this situation, analyse how the queries the running and attempt to make them as efficient as possible.
1)Is the query running large table scans?
2)Are indexes set up effectively , with statistics up to date.
Read more on a systematic approach to tuning a query - http://www.sqlserver-dba.com/2012/11/sql-server-how-to-troubleshoot-a-slow-running-query.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

thisisgerald
Starting Member

Philippines
4 Posts

Posted - 01/06/2015 :  02:21:34  Show Profile  Reply with Quote
Thanks Jackv!

I'm not a DBA and only know basic (developer side) on SQL.

Below is the table structure and in my understanding, it is okay. Is it?

Column Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullnSource Collation
BR char no 2 no no no SQL_Latin1_General_CP1_CI_AS
PRO char no 6 no no no SQL_Latin1_General_CP1_CI_AS
TYP char no 2 no no no SQL_Latin1_General_CP1_CI_AS
ARG char no 3 no no no SQL_Latin1_General_CP1_CI_AS
DEA char no 8 no no no SQL_Latin1_General_CP1_CI_AS
SEQ char no 4 no no no SQL_Latin1_General_CP1_CI_AS
GLN char no 15 no no no SQL_Latin1_General_CP1_CI_AS
COS char no 10 no no no SQL_Latin1_General_CP1_CI_AS
CC char no 3 no no no SQL_Latin1_General_CP1_CI_AS
BEI char no 1 no no no SQL_Latin1_General_CP1_CI_AS
COD char no 4 no no no SQL_Latin1_General_CP1_CI_AS
QUA char no 1 no no no SQL_Latin1_General_CP1_CI_AS
EFF datetime no 8 no (n/a) (n/a) NULL
POS datetime no 8 no (n/a) (n/a) NULL
CMN char no 10 yes no yes SQL_Latin1_General_CP1_CI_AS
DRC char no 2 yes no yes SQL_Latin1_General_CP1_CI_AS
AMO numeric no 9 19 4 yes (n/a) (n/a) NULL
SME char no 7 yes no yes SQL_Latin1_General_CP1_CI_AS
SAC char no 15 yes no yes SQL_Latin1_General_CP1_CI_AS
DES char no 70 yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment NotForReplication
No identity column defined. NULL NULL NULL

RowGuidCol
No rowguidcol column defined.

Data_located_on_filegroup
PRIMARY

index_name index_description index_keys
QCUPPK clustered, unique, primary key located on PRIMARY BR, PRO, TYP, ARG, DEA, SEQ, GLN, COS, CC, BEI, COD, QUA, EFF, POS

contraint_type constraint_name delete_action update_action status_enabled status_for_replication contraint_keys
PRIMARY KEY (clustered) QCUPPK (n/a) (n/a) (n/a) (n/a) BR, PRO, TYP, ARG, DEA, SEQ, GLN, COS, CC, BEI, COD, QUA, EFF, POS


Thanks!

thisisgerald
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 01/07/2015 :  01:34:48  Show Profile  Visit jackv's Homepage  Reply with Quote
There's a few different strategies you can apply to deal with queries on tables. Does the select statement used , utilise the indexes you have set up? Use the execution plan to analyse

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

thisisgerald
Starting Member

Philippines
4 Posts

Posted - 01/07/2015 :  20:47:49  Show Profile  Reply with Quote
Here is the execution plan and I think it is okay because only those
indexed fields are selected by a simple select query.




Edited by - thisisgerald on 01/09/2015 01:14:32
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 01/08/2015 :  01:25:14  Show Profile  Visit jackv's Homepage  Reply with Quote
@thisisgerald - the execution plan is not appearing.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/08/2015 :  07:10:39  Show Profile  Reply with Quote
you say that another user is having a problem in a different program. But you haven't yet said what the problem is the other user is experiencing.
Go to Top of Page

thisisgerald
Starting Member

Philippines
4 Posts

Posted - 01/09/2015 :  01:24:09  Show Profile  Reply with Quote
@jackv
Cannot insert picture.

@gbritton
The other user problem is sometimes time-out expired or connected to "blocking" issue. As stated in the scenario above, program 2 is accessed my many users (same functions) and only selected user is encountering the problem.

Thanks!
thisisgerald
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/09/2015 :  06:51:46  Show Profile  Reply with Quote
can you post some code? For example, stored procedures that are used by the programs use accessing the database or any pass through queries that are used.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000