Million of Records - SQL Server Forums

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

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Million of Records

Author

Topic

thisisgerald
Starting Member

4 Posts

Posted - 2015-01-06 : 01:02:26

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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-01-06 : 01:49:34

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

thisisgerald
Starting Member

4 Posts

Posted - 2015-01-06 : 02:21:34

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-01-07 : 01:34:48

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

thisisgerald
Starting Member

4 Posts

Posted - 2015-01-07 : 20:47:49

Here is the execution plan and I think it is okay because only those
indexed fields are selected by a simple select query.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-01-08 : 01:25:14

@thisisgerald - the execution plan is not appearing.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-08 : 07:10:39

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.

thisisgerald
Starting Member

4 Posts

Posted - 2015-01-09 : 01:24:09

@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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-09 : 06:51:46

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources